CODEWITHSUNDEEP
sqlpostgresql
Peter T. Walker
Peter T. Walker

Reputation: 332

Is there a way to return the max value of column in SQL?

If I have a table...

name | day | showers
-----|-----|--------
John | mon | 1
John | tue | 2
John | wed | 1
Pete | fri | 3

and I want to return the table of each persons day with the most showers...

name | day | showers
-----|-----|--------
John | tue | 2
Pete | fri | 3

How would I do this?

I thought I could do...

SELECT name, day FROM table GROUP BY name WHERE showers=max(showers)

but I get a syntax error due to day not being in the group by clause

Upvotes: 0

Views: 75

Answers (3)

Rute Lemos
Rute Lemos

Reputation: 191

You can use this query:

SELECT name, days, showers
FROM test
WHERE showers =
(SELECT max(showers) FROM test i WHERE i.name = test.name)

A correlated subquery is one way of reading every row in a table and comparing values in each row against related data. It is used whenever a subquery must return a different result or set of results for each candidate row considered by the main query. In other words, you can use a correlated subquery to answer a multipart question whose answer depends on the value in each row processed by the parent statement.

So from the original tableyou get this result

So from the original table on the left, by running this query, you get the result in the table on the right

Upvotes: 0

Rajat
Rajat

Reputation: 5803

You could use an uncorrelated subquery. This will keep the ties as well

select *
from t 
where (name, showers) in (select name, max(showers)
                          from t
                          group by name);

Can also be expressed as a correlated subquery

select *
from t t1
where showers= (select max(t2.showers)
                from t t2
                where t1.name=t2.name);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Use distinct on:

select distinct on (name) t.*
from t
order by name, showers desc;

This returns exactly one row per name. If you want duplicates if there are ties, then use rank() or dense_rank():

select t.*
from (select t.*,
             rank() over (partition by name order by showers desc) as seqnum
      from t
     ) t
where seqnum = 1;

Upvotes: 2

Related Questions