Search code examples
sqldatabasepostgresqlgreatest-n-per-grouppsql

How to find an attribute's largest value based on another attribute?

Owner | Price
-------------
James | 8000
David | 1500
James | 20000
Kim   | 4500
Kim   | 9500

For example if I have the above table displaying the owner's name of some watches and their price. How could I find out the most expensive watch each person has? So the result table should have 3 tuples in that case.

Solution

  • GROUP BY and MAX can get you what you want.

    select Owner, MAX(price) as maxPrice
FROM Table1
Group by Owner