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?

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. — Taryn
– Taryn, Commented Mar 11, 2013 at 11:45

Taryn · Accepted Answer · 2013-03-11 13:13:31Z

7

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

edited Mar 11, 2013 at 13:13

answered Mar 11, 2013 at 11:48

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vladimir S. Over a year ago

Works totally great. Thank you!

Clodoaldo Neto · Accepted Answer · 2013-03-11 13:30:01Z

1

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

answered Mar 11, 2013 at 13:30

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Collectives™ on Stack Overflow

Select columns by choosing maximum value in multiple columns in SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related