Select columns by choosing maximum value in multiple columns in SQL

Question

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?

Solution 1

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

Solution 2

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

Comments

[Taryn]

Please specify the RDBMS that you are targeting by adding the appropriate tag (Oracle, SQL Server, MySQL, etc.). There may be answers that take advantage of language or product features that are not universally supported. Also, by tagging it with a specific RDBMS, your question may receive attention from people better suited to answer it.

[Vladimir S.]

Works totally great. Thank you!