Multiple choice technology databases

Which is the correct query to find the Min, Max and 2nd Max in Sql server?

  1. select min(field4) as Min,Max(field4) as Max,(select Max(field4) from table1 where field4 not in(Select Min(field4) from table1)) as 2nmax from table1

  2. select min(field4) as Min,Max(field4) as Max,(select Min(field4) from table1 where field4 not in(Select Max(field4) from table1)) as 2nmax from table1

  3. select min(field4) as Min,Max(field4) as Max,(select Max(field4) from table1 where field4 not in(Select Max(field4) from table1)) as 2nmax from table1

  4. None of the Above
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To find the 2nd maximum value, we need to find the maximum value from the set of records that excludes the overall maximum. The query in option C correctly does this: the subquery gets MAX(field4) WHERE field4 is NOT IN the list containing the single maximum value. This returns the second highest value. Options A and B use MIN incorrectly in the subquery, which would give the minimum value, not the second maximum.

Keep practicing — related questions