Get first record based on time in PostgreSQL-CodePudding

DO we have a way to get first record considering the time.

example get first record today, get first record yesterday, get first record day before yesterday ...

Note: I want to get all records considering the time

sample expected output should be

first_record_today,
first_record_yesterday,..

CodePudding user response：

As I understand the question, the "first" record per day is the earliest one.

For that, we can use RANK and do the PARTITION BY the day only, truncating the time.

In the ORDER BY clause, we will sort by the time:

SELECT sub.yourdate FROM ( 
  SELECT yourdate, 
    RANK() OVER 
      (PARTITION BY DATE_TRUNC('DAY',yourdate) 
       ORDER BY DATE_TRUNC('SECOND',yourdate)) rk
    FROM yourtable
  ) AS sub 
  WHERE sub.rk = 1
  ORDER BY sub.yourdate DESC;

In the main query, we will sort the data beginning with the latest date, meaning today's one, if available.

We can try out here: db<>fiddle

If this understanding of the question is incorrect, please let us know what to change by editing your question.

A note: Using a window function is not necessary according to your description. A shorter GROUP BY like shown in the other answer can produce the correct result, too and might be absolutely fine. I like the window function approach because this makes it easy to add further conditions or change conditions which might not be usable in a simple GROUP BY, therefore I chose this way.

EDIT because the question's author provided further information:

Here the query fetching also the first message:

SELECT sub.yourdate, sub.message FROM ( 
  SELECT yourdate, message,
    RANK() OVER (PARTITION BY DATE_TRUNC('DAY',yourdate) 
    ORDER BY DATE_TRUNC('SECOND',yourdate)) rk
    FROM yourtable
  ) AS sub 
  WHERE sub.rk = 1
  ORDER BY sub.yourdate DESC;

Or if only the message without the date should be selected:

SELECT sub.message FROM ( 
  SELECT yourdate, message,
    RANK() OVER (PARTITION BY DATE_TRUNC('DAY',yourdate) 
    ORDER BY DATE_TRUNC('SECOND',yourdate)) rk
    FROM yourtable
  ) AS sub 
  WHERE sub.rk = 1
  ORDER BY sub.yourdate DESC;

Updated fiddle here: db<>fiddle