selecting multiple recent values in a single sql statement

Question

I have a table that has data that looks something like this:

data_type, value, datetime
World of Warcraft, 500, 2012-12-02
Quake 3, 1500, 2013-12-02
Quake 3, 1400, 2013-02-04
World of Warcraft, 1200, 2013-05-20
Final Fantasy, 100, 2013-02-03
Final Fantasy, 500, 2013-03-05

What I want to select is something like the following:

data_type, value
World of Warcraft, 1200
Quake 3, 1500
Final Fantasy, 500

select  
  most recent value for 'World of Warcraft', 
  most recent value for 'Quake 3', 
  most recent value for 'Final Fantasy'

So I get the most recent value of each of these in a single statement rather than having to separate them out. How would I go about doing this?

possible duplicate of [Selecting multiple Max() values using a single SQL Statement - postgresql](http://stackoverflow.com/questions/18494829/selecting-multiple-max-values-using-a-single-sql-statement-postgresql) — , Aug 28 '13 at 19:11
i did not, this one is asking about selecting the most recent where as the other is asking for the maximum value http://stackoverflow.com/questions/18494829/selecting-multiple-max-values-using-a-single-sql-statement-postgresql If you read the comments the person who helped was the one who actually suggesting a new question. But thanks for the help.... — user2146933, Aug 28 '13 at 19:12
@Lamak: I noticed. I just looked so identical to the other one... — , Aug 28 '13 at 19:14
@a_horse_with_no_name I asked the user to post a new question because the requirements changed from the other one. — Taryn, Aug 28 '13 at 19:28

score 2 · Answer 1 · edited Aug 28 '13 at 19:35

2

This should do:

SELECT *
FROM (  SELECT  *, 
                ROW_NUMBER() OVER(PARTITION BY data_type 
                                  ORDER BY datetimecol DESC) AS RN
        FROM YourTable) AS A
WHERE RN = 1

There is actually a function last_value on Postgresql, but I'm not familiar with it.

If you want the data to appear in columns, then you can use:

SELECT 
    max(case when data_type='World of Warcraft' then value end) WorldofWarcraft,
    max(case when data_type='Quake 3' then value end) Quake3,
    max(case when data_type='Final Fantasy' then value end) FinalFantasy
FROM (  SELECT  data_type, value, datetime, 
                ROW_NUMBER() OVER(PARTITION BY data_type 
                                  ORDER BY datetimecol DESC) AS RN
        FROM YourTable) AS A
WHERE RN = 1

edited Aug 28 '13 at 19:35

Taryn

242,637
56
362
405

answered Aug 28 '13 at 19:12

Lamak

69,480
12
108
116

I believe they want the results in separate columns as well, so you might want to pivot it. :) – Taryn Aug 28 '13 at 19:31
@bluefeet Oh, didn't realize about that, since the first result set he posted was on different rows. But you are right that the pseudo query he posted hints to pivoting. Too much work, we need someone with more expertise on that :-) – Lamak Aug 28 '13 at 19:33
Just trying to be helpful! – Taryn Aug 28 '13 at 19:42

score 0 · Answer 2 · edited May 23 '17 at 11:57

Another case for DISTINCT ON. Much simpler and faster:

SELECT DISTINCT ON (data_type)
       data_type, value
FROM   tbl
ORDER  BY data_type DESC, datetime DESC;

You can include or exclude any columns you like. More explanation and details:
Select first row in each GROUP BY group?

I use data_type DESC, since your example indicates you want descending order for your data types. Omit DESC if you don't care.

You should also normalize your data. Create a lookup table for your data types and only store the foreign key in your main table. Example.

selecting multiple recent values in a single sql statement

2 Answers2