SQL - Find max value and then corresponding column

Question

SQL query: 3 column table, searching "John" 's max value returning City only.
Data:

Name | City | Value <br>
John | LDN | 50 <br>
Joey | MCR | 12<br>
Dave | BHM | 5<br>
John | NTH | 56 <br>

Desired result: NTH (4th row)

How can I achieve this? Thanks in advance.

T-SQL. If John, LDN has 56? Luckily I'm not too worried about this but it does raise a good point as to what would take priority! — braxofribz, Aug 24 '20 at 11:50
Possible duplicate of [TSQL show only first row](https://stackoverflow.com/questions/12688576/tsql-show-only-first-row). Just order by Value instead of Date. In [how does SELECT TOP works when no order by is specified?](https://stackoverflow.com/questions/15245322/how-does-select-top-works-when-no-order-by-is-specified) the answer is in the Question and in [Select Top N Records Ordered by X, But Have Results in Reverse Order](https://stackoverflow.com/questions/2572496/select-top-n-records-ordered-by-x-but-have-results-in-reverse-order) the answer is in the subquery. — Scratte, Aug 26 '20 at 06:27

score 0 · Accepted Answer · answered Aug 24 '20 at 10:40

0

You can use row_number()

select city from
(
select *, row_number() over(partition by name order by value desc) as rn
from tablename
)A where rn=1 and name='John'

Alternatively,

select city from tablename t 
where name='John' and value = (select max(value) from tablename t1 where t.name=t1.name)

answered Aug 24 '20 at 10:40

Fahmi

1

Thanks Fahmi, feels like a good introduction for myself to the Row_number function! – braxofribz Aug 24 '20 at 11:52

score 0 · Answer 2 · answered Aug 24 '20 at 10:50

0

You can use order by and some way of limiting the results:

select t.*
from t
order by value desc
fetch first 1 row only;

Some databases use select top (1) or limit 1 to limit to a single row.

answered Aug 24 '20 at 10:50

Gordon Linoff

2 Answers2