Get the row with the max value in sql

Question

I want to get the row with the maximum value on a column. I my case I want to ptint only X that has 31. This is my code and also the output.

create temp view Private(marca) as
    Select a.marca, count(*) as totaleNoleggi, count(distinct a.targa) as totaleAuto, sum(extract(hour from (n.fine-n.inizio))) as totOre
    from auto a join noleggio n on a.targa=n.targa
    group by a.marca;

select marca, totOre
from Private 
group by marca,totore
order by totore desc
limit 1;

    marca   totalenoleggi   totaleauto  totore
1   Audi    1                  1          7
2   BMW     5                  4          7
3   VW      2                  1          1
4   X       2                  1          31

    marca   totore
1   X         31

But it's a wrong approach, for example without the X , Audi or BMW has 7 but my select will print just the first Audi. So it's another method to get the maximum value

https://stackoverflow.com/questions/tagged/greatest-n-per-group+postgresql — a_horse_with_no_name, Aug 31 '17 at 12:13

score 4 · Answer 1 · answered Aug 31 '17 at 11:36

4

You can use subquery

select marca, totOre
from Private 
where totore = (select max(totore) from private)

Hope it will help.

answered Aug 31 '17 at 11:36

Pirate

2,672
4
21
39

score 2 · Accepted Answer · answered Aug 31 '17 at 11:51

2

I feel that ALL construct can be useful here

SELECT marca, totOre
FROM Private 
WHERE totore >= ALL(select totore from private)

answered Aug 31 '17 at 11:51

Radim Bača

10,526
1
18
32

Get the row with the max value in sql

2 Answers2