PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Select max time of datetime column group by other datetime column

I want to have a result of unique row of id, date_col1, with max (or last) of date_col2, and the value contained in the same row as max(date_col2).

My table is like this sample:

id date_col1 date_col2 value
1 2023-01-02 12:00:00 2023-01-02 12:00:00 10
1 2023-01-02 12:00:00 2023-01-02 13:00:00 3
1 2023-01-02 13:00:00 2023-01-02 12:00:00 5
1 2023-01-02 13:00:00 2023-01-02 13:00:00 1
1 2023-01-02 13:00:00 2023-01-02 14:00:00 7
1 2023-01-02 13:00:00 2023-01-02 15:00:00 4
2 2023-01-02 12:00:00 2023-01-02 12:00:00 5
2 2023-01-02 12:00:00 2023-01-02 13:00:00 1
2 2023-01-02 13:00:00 2023-01-02 13:00:00 7
2 2023-01-02 13:00:00 2023-01-02 18:00:00 4

Desired result:

id date_col1 date_col2 value
1 2023-01-02 12:00:00 2023-01-02 13:00:00 3
1 2023-01-02 13:00:00 2023-01-02 15:00:00 4
2 2023-01-02 12:00:00 2023-01-02 13:00:00 1
2 2023-01-02 13:00:00 2023-01-02 18:00:00 4

Right now I can get that result with this query:

with max_date_col2 as (select id,date_col1,max(date_col2) as max_col1 from table
group by id,date_col1 order by date_col1)
select table.* from max_date_col2 max join table on max.id=table.id and max.date_col1=table.date_col1 and max.date_col2=table.date_col2;

But it seems inefficient. Is there some way with one SELECT query, without additional CTE?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    it was duplicated question: Retrieving the last record in each group - MySQL

    got a hint from above and got it right using leftjoin and <.


  2. 0

    Don’t look to MySQL solutions if you are running Postgres.
    DISTINCT ON is much simpler:

    SELECT DISTINCT ON (id, date_col1)
       id, date_col1, date_col2, value
FROM   tbl
ORDER  BY id, date_col1, date_col2 DESC;

    See:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search