CODEWITHSUNDEEP
sqlpostgresql
Vladimir S.
Vladimir S.

Reputation: 470

Select columns by choosing maximum value in multiple columns in SQL

Imagine the following exemplary table:

time      name     value1 value2
12:00     Hans     2      4
12:30     Hans     2      4
13:00     Hans     3      5
14:00     Peter    4      4
15:00     Peter    4      4

I want to filter by maximum time stamp and name.

Meaning I want to get

13:00     Hans     3      5
15:00     Peter    4      4

Using select max(time),name,value1,value2 from table group by name does not work. It tells me to use an aggregate function or group by on value1 and value2 also.

If I group by name, value1 and value2 then I get the following result though since value1 and value2 are different for two rows of Hans:

12:30     Hans     2      4
13:00     Hans     3      5
15:00     Peter    4      4

What is the solution?

Upvotes: 4

Views: 16006

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125574

With distinct on it is quite simple. SQL Fiddle

select distinct on (name)
    "time", name, value1, value2
from t
order by t.name, t."time" desc

Upvotes: 1

Taryn
Taryn

Reputation: 247880

You can use a subquery to get the max(time) for each name and then join that back to your table to get the final result:

select t1.time,
  t1.name,
  t1.value1,
  t1.value2
from yourtable t1
inner join
(
  select max(time) MaxTime, name
  from yourtable
  group by name
) t2
  on t1.time = t2.maxtime
  and t1.name = t2.name

Depending on the database that you are using, if you can apply a windowing function like row_number() or rank(), then your query would be similar to the following:

select time, name, value1, value2
from
(
  select time, name, value1, value2,
    rank() over(partition by name order by time desc) rn
  from yourtable
) src
where rn = 1

See SQL Fiddle with Demo of both queries

Upvotes: 7

Related Questions