Pulling max values grouped by a variable with other columns in SQL

Question

Say I have three columns in a very large table: a timestamp variable (last_time_started), a player name (Michael Jordan), and the team he was on the last time he started (Washington Wizards, Chicago Bulls), how do I pull the last time a player started, grouped by player, showing the team? For example:

before table and after table

if I did

select max(last_time_started), player, team
from table
group by 2

I would not know which team the player was on when he played his last game, which is important to me.

It seems to me that `group by player, team` instead of `group by 2` could do the job. — Raffael, Oct 13 '15 at 18:08
Possible duplicate of [How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?](http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql) — Kirill Kin, Oct 13 '15 at 19:00

score 1 · Answer 1 · answered Oct 13 '15 at 18:15

In Postgres the most efficient way is to use distinct on():

SELECT DISTINCT ON (player) 
       last_time_started, 
       player, 
       team, 
FROM the_table
ORDER BY player, last_time_started DESC;

Using a window function is usually the second fastest solution, using a join with a derived table is usually the slowest alternative.

Kirill Kin · Answer 2 · 2015-10-13T18:20:33.607

Try this solution

select s.*
from table s
     inner join (
        select max(t.last_time_started) as last_time_started, t.player
        from table t
        group by t.player) v on s.player = t.player and s.last_time_started = t.last_time_started

Also this approach should be faster, because it does not contain join

select v.last_time_started,
       v.player,
       v.team
from (
    select t.last_time_started,
           t.player,
           t.team,
           row_number() over (partition by t.player order by last_time_started desc) as n
    from table t
) v
where v.n = 1

score 0 · Answer 3 · answered Oct 13 '15 at 18:13

Here's a couple of ways to do this in Postgres:

With windowing functions:

SELECT last_time_started, player, team
FROM
    (
        SELECT
            last_time_started, 
            player, 
            team, 
            CASE WHEN max(last_time_started) OVER (PARTITION BY PLAYER) = last_time_started then 'X' END as max_last_time_started
        FROM table
    )
WHERE max_last_time_started = 'x';

Or with a correlated subquery:

SELECT last_time_started, player, team
FROM table t1
WHERE last_time_started = (SELECT max(last_time_started) FROM table WHERE table.player = t1.player);

Pulling max values grouped by a variable with other columns in SQL

3 Answers3