11

Table Essentially looks like:

Serial-ID, ID, Date, Data, Data, Data, etc.

There can be Multiple Rows for the Same ID. I'd like to create a view of this table to be used in Reports that only shows the most recent entry for each ID. It should show all of the columns.

Can someone help me with the SQL select? thanks.

DiscontentDisciple
  • 466
  • 1
  • 6
  • 20

3 Answers3

25

There's about 5 different ways to do this, but here's one:

SELECT *
FROM yourTable AS T1 
WHERE NOT EXISTS(
    SELECT *
    FROM yourTable AS T2
    WHERE T2.ID = T1.ID AND T2.Date > T1.Date
)

And here's another:

SELECT T1.*
FROM yourTable AS T1
LEFT JOIN yourTable AS T2 ON
(
    T2.ID = T1.ID 
    AND T2.Date > T1.Date
)
WHERE T2.ID IS NULL

One more:

WITH T AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Date DESC) AS rn
    FROM yourTable
)
SELECT * FROM T WHERE rn = 1

Ok, i'm getting carried away, here's the last one I'll post(for now):

WITH T AS (
    SELECT ID, MAX(Date) AS latest_date
    FROM yourTable
    GROUP BY ID
)
SELECT yourTable.*
FROM yourTable
JOIN T ON T.ID = yourTable.ID AND T.latest_date = yourTable.Date
J Cooper
  • 4,828
  • 3
  • 36
  • 39
  • My bet is that the version with row_number() is going to be the fastest. –  Feb 27 '12 at 23:13
  • In my case: OVER - 8272.890 ms(external merge Disk: 274968kB), GROUP BY - 2049.451 ms(Sort Method: quicksort Memory: 105kB). ~3M records, Postgres 11, AWS db.t3.medium. So if you can't increase work_mem value for some reasons GROUP BY can win. – El Ruso Mar 27 '21 at 18:09
15

I would use DISTINCT ON

CREATE VIEW your_view AS
SELECT DISTINCT ON (id) *
FROM your_table a
ORDER BY id, date DESC;

This works because distinct on suppresses rows with duplicates of the expression in parentheses. DESC in order by means the one that normally sorts last will be first, and therefor be the one that shows in the result.

https://www.postgresql.org/docs/10/static/sql-select.html#SQL-DISTINCT

Jasen
  • 11,837
  • 2
  • 30
  • 48
0

This seems like a good use for correlated subqueries:

CREATE VIEW your_view AS
SELECT *
FROM your_table a
WHERE date = (
    SELECT MAX(date)
    FROM your_table b
    WHERE b.id = a.id
)

Your date column would need to uniquely identify each row (like a TIMESTAMP type).

bhamby
  • 15,112
  • 1
  • 45
  • 66