How do I find the largest value in a column in postgres sql?

Question

For example:

name | weight
jon    100    
jane   120    
joe    130

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

score 106 · Answer 1 · answered Dec 05 '12 at 11:20

106

SELECT name FROM tbl ORDER BY weight DESC LIMIT 1

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

answered Dec 05 '12 at 11:20

raveren

17,799
12
70
83

5

Good answer. I also want to note that if weight have an index, sort will performed on index and will be faster. – bmlkc Aug 17 '13 at 04:10
5

Nice answer, however it won't return two results if two people have the same weight. – Standaa - Remember Monica Apr 21 '16 at 12:23
4

LIMIT 1 requires a full table scan. This is _not_ performant with lots of rows. Try running `EXPLAIN` on your queries. – Volte Dec 29 '16 at 02:46
2

If there is any NULL in weight, it returns NULL – alextc Jul 26 '18 at 05:16
3

Weights with NULL values can be filtered out by using WHERE: `SELECT name FROM tbl WHERE weight IS NOT NULL ORDER BY weight DESC LIMIT 1`. – Orienteerix Jun 28 '20 at 12:23
@Standaa-RememberMonica: Simply change the `LIMIT` to whatever value you feel is appropriate, e.g., `SELECT name FROM tbl ORDER BY weight DESC LIMIT 10`. – ToJo Aug 26 '21 at 20:39
sorry, very bad solution: what if you have two similar highest values? – simUser Jan 27 '22 at 11:53

score 50 · Accepted Answer · answered Feb 06 '11 at 00:58

50

Use this:

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

answered Feb 06 '11 at 00:58

Hao

8,047
18
63
92

wow, how silly of me not to think of using a nested query! thanks! – Feb 06 '11 at 01:00
8

Depending on the number of rows with values of "weight" equal to the maximum value, this may return more than one name. Just FYI. – Milen A. Radev Feb 06 '11 at 01:32
How do you make it return only one of the rows with the max value (doesn't matter how it chooses)? – Nick Brady Jun 22 '16 at 01:38
select **top 1** name from tbl where weight = (select max(weight) from tbl) – Frostytheswimmer Jun 08 '17 at 14:24

alexkovelsky · Answer 3 · 2020-03-17T17:15:42.487

7

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 name, max(weight), (array_agg(day ORDER BY weight DESC))[1] FROM tbl GROUP BY name

edited Mar 17 '20 at 17:15

answered Nov 03 '18 at 16:30

alexkovelsky

3,880
1
27
21

1

this is most elegant solution !! – Robert TheSim Mar 06 '20 at 15:49

Matt Kleinsmith · Answer 4 · 2017-12-02T21:40:35.133

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

How do I find the largest value in a column in postgres sql?

4 Answers4

Linked