I really appreciate if you can share the link with your friends via Facebook,twitter,blog etc.

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..
i.e,
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..
SELECT 
T11.Id,
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)