10 August 2011

Eliminate Duplicate Records

Hi guys,
This time our task is to remove or eliminate duplicate entries from a sql Table.
Consider we have a Table Say Employee which contain some duplicate entries,and we have to eliminate the duplicate ones.

So lets start thinking..

09 August 2011

Get Max Value Without using Max And Order by

Hello Guys, I was busy with some stuffs from last few days,actually was looking for a job change.
but now i am back.
In one of my interview with some good reputed company,interviewer asked me a very interesting question,
      He wanted me to write a query to get maximum amount without using order By and max keyword from a table T1 which contain 2 Columns Say Id and amount
      As soon as he Kept this question,i bethink,some time before i encountered a situation where i have to find the maximum number in array using .net,
And i thought same approach can be applied even here..
Declare @MaxValue int
Select @MaxValue=(case when @MaxValue>Amount then @MaxValue else Amount end) From T1
Select @Maxvalue
Interviewer impressed by this, but he still wanted the output, i.e, the max value using a single query.
After pondering for sometime,I got a respond from interviewer that "I already found,what is looking on u,so leave it,will move forward,I know u will do it."
When i came out of intreview panel,i again statred to think the solution for that,and ultimatly i found that even.
       on the next day i asked my friends the same question,and one of my friend accidently found one more query
Declare @MaxValue int
Select @MaxValue=Amount From T1 Group by Amount
Select @MaxValue
I was ok with the query,because the desired result was there...and then i put the same condition whcih was put by  interviewer in my case,i.e i need the result in single query..
and the query was ultimatly..
T11.Amount as MaxAmount
FROM T1 AS T11 left outER JOIN
T1 AS T12 ON T11.Amount < T12.Amount 
GROUP BY T11.Amount, T11.Id
having COUNT(T12.Amount)=0

(Try to put more solutions if you have any..
also if in case any body have any such or related to any technology any scenario, put it them along with your solution as a comment.I will help you to share with the world)

08 August 2011

Sql Practice - 1

Hi all,
One of my friend have been asked in of the interview following question,

Consider you have at table Say TblEmployee with a column say EmployeeName
which contain Records as A,B,A,A,B,C,B,C,A

your task is to write a query which will change the EmployeeName A to B and B to A.
Means after the query been executed Records will be as B,A,B,B,A,C,A,C,B
Think what will be the query..and try to post some answers..after two day i will post the answer

Things are upgraded

My Dear readers, I am really thankful for being supportive all these years. This site was the first blog site I ever created in my life...