CODEWITHSUNDEEP
sqlsql-serverdatabaseinner-join
Tony
Tony

Reputation: 117

SQL - get max result

Assume there is a table name "test" below:

name    value
n1        1
n2        2
n3        3

Now, I want to get the name which has the max value, I have some solution below:

Solution 1:

SELECT TOP 1 name 
FROM test 
ORDER BY value DESC

solution 2:

SELECT name 
FROM test 
WHERE value = (SELECT MAX(value) FROM test);

Now, I hope use join operation to find the result, like

SELECT name 
FROM test 
INNER JOIN test ON...

Could someone please help and explain how it works?

Upvotes: 1

Views: 80

Answers (2)

trincot
trincot

Reputation: 350137

First, note that solutions 1 and 2 could give different results when value is not unique. If in your test data there would be an additional record ('n4', 3), then solution 1 would return either 'n3' or 'n4', but solution 2 would return both.

A solution with JOIN will need aliases for the table, because as you started of, the engine would say Ambiguous column name 'name'.: it would not know whether to take name from the first or second occurrence of the test table.

Here is a way to complete the JOIN version:

SELECT    t1.name 
FROM      test t1
LEFT JOIN test t2
       ON t2.value > t1.value
WHERE     t2.value IS NULL;

This query takes each of the records, and checks if any records exist that have a higher value. If not, the first record will be in the result. Note the use of LEFT: this denotes an outer join, so that records from t1 that have no match with t2 -- based on the ON condition -- are not immediately rejected (as would be the case with INNER): in fact, we want to reject all the other records, which is done with the WHERE clause.

A way to understand this mechanism, is to look at a variant of the query above, which lacks the WHERE clause and returns the values of both tables:

SELECT    t1.value, t2.value  
FROM      test t1
LEFT JOIN test t2
       ON t2.value > t1.value

On your test data this will return:

t1.value t2.value
    1      2
    1      3
    2      3
    3    (null)

Note that the last entry would not be there if the join where an INNER JOIN. But with the outer join, one can now look for the NULL values and actually get those records in the result that would be excluded from an INNER JOIN.

Note that this query will give the same result as solution 2 when there are duplicate values. If you want to have also only one result like with solution 1, it suffices to add TOP 1 after SELECT.

Here is a fiddle.

Alternative with pure INNER JOIN

If you really want an INNER join, then this will do it. Again the TOP 1 is only needed if you have non-unique values:

SELECT    TOP 1 t1.name 
FROM      test t1
INNER JOIN (SELECT Max(value) AS value FROM test) t2
       ON t2.value = t1.value;

But this one really is very similar to what you did in solution 2. Here is fiddle for it.

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172398

If you are looking for JOIN then

SELECT T.name, T.value
    FROM test T
INNER JOIN 
(   SELECT T1.name, T1.value ,
        RANK() OVER (PARTITION BY T1.name ORDER BY T1.value) N
    FROM test T1
    WHERE T1.value IN (SELECT MAX(t2.value) FROM test T2)
)T3 ON T3.N = 1 AND T.name = T3.name

FIDDLE DEMO

or 

select  name, value
from
(
  select  name, value,
    row_number() over(order by value desc) rn
  from test
) src
where rn = 1

FIDDLE DEMO

Upvotes: 3

Related Questions