How do i find the largest value in a column in pos

问题:

For example:

name | weight
jon    100    
jane   120    
joe    130

How do I only return the name of the person with the largest weight?

回答1:

Use this:

select name
from tbl
where weight = (select max(weight) from tbl)

回答2:

SELECT name FROM tbl ORDER BY weight DESC LIMIT 1

Much more performant than the other answer and results in one row only.

回答3:

ORDER BY DESC puts rows with null values at the top.

To avoid returning results corresponding to null values:

SELECT name FROM tbl WHERE weight = (SELECT MAX(weight) FROM tbl);

Note: This query will return multiple results if multiple people have a weight equal to the maximum weight. To grab just one, add LIMIT 1 to the end of the query.

---

Acknowledgements and more information:

Why do NULL values come first when ordering DESC in a PostgreSQL query?

MIN/MAX vs ORDER BY and LIMIT

Postgres MAX Function

回答4:

If you need to find multiple rows, e.g. date on which each person had maximum weight:

name | weight | day
don    110      1
don    120      20
don    110      30
joe    90       1
joe    80       15
joe    85       30

i.e. for "don" you want to get "don | 120 | 20" and for joe you want "joe | 90 | 1", then you can write:

SELECT (array_agg(name ORDER BY weight DESC))[1], max(weight), (array_agg(day ORDER BY weight DESC))[1] FROM tbl