CODEWITHSUNDEEP
postgresqlaggregate-functions
Thanos Darkadakis
Thanos Darkadakis

Reputation: 1729

Getting value of column1 for aggregate(column2)

Let's assume that I have a table (myTable) with 4 columns (id, player_id, myDate, points). I want to find the points that all players had on a certain day

id     | player_id | myDate     | points
------ | --------- | ---------- | -----
1      | 1         | 2016-05-06 | 50
2      | 1         | 2016-10-16 | 60
3      | 1         | 2016-09-12 | 70
4      | 1         | 2016-09-16 | 30
5      | 2         | 2016-02-05 | 10
6      | 2         | 2016-10-18 | 20
7      | 2         | 2017-01-02 | 30
8      | 2         | 2016-08-16 | 40

Expected result for 1/1/2017:

id     | player_id | myDate     | points
------ | --------- | ---------- | -----
2      | 1         | 2016-10-16 | 60
6      | 2         | 2016-10-18 | 20

What I would do until now is something like the following: (first find the last date of each player and then the score of that date)

select id, q.player_id, points from
(
    select player_id, max(myDate) as maxDate from myTable
    where myDate<'2017-01-01'
    group by player_id
) q
left join myTable
on q.maxDate=myTable.mydate 
and q.player_id=myTable.player_id

Of course, in case I have 2 records of the same player on the same date, I would get 2 rows for this player_id and I would have to add more code to the query to solve this problem.

Apart from that I have to self join (my table has 1.000.000 records at the moment - tbh it's a view) and this could not be fast.

I have lately been using another query:

select id,player_id,points from 
(
select rank() over 
    (partition by player_id 
    order by myDate desc, id desc) as r, * 
from myTable
where myDate <'2017-01-01'
) q
where r=1

Is there a better (= more efficient) way to achieve what I want?

Upvotes: 0

Views: 36

Answers (1)

klin
klin

Reputation: 121524

Use DISTINCT ON.

select distinct on (player_id) *
from my_table
where my_date < '2017-01-01'
order by player_id, my_date desc;

Test it here.

Upvotes: 1

Related Questions