Search code examples
sqlpostgresqlgreatest-n-per-group

SQL first row by creation_date

I need some help here if you please. I always have difficulties getting the very first row from the table.

Each user might have multiple order_id and each order_id has his own date. I need for each user to show only their first order_id and date, any suggestions? Table example:

User order_id date
Andrew 1242 2021-01-01
Andrew 1252 2021-01-03
Mike 1255 2021-01-04
Andrew 1256 2021-01-04
John 1266 2021-01-04
Andrew 1277 2021-01-06
John 1287 2021-01-06
Johhny 1288 2021-01-06

And I need to get the following:

User order_id date
Andrew 1242 2021-01-01
Mike 1255 2021-01-04
John 1266 2021-01-04
Johhny 1288 2021-01-06
Solution 
  •  SELECT X.USER,X.ORDER_ID,X.DATE FROM 
 (
    SELECT C.USER,C.ORDER_ID,C.DATE,
       ROW_NUMBER()OVER(PARTITION BY C.USER ORDER BY C.date ASC)XCOL
      FROM YOUR_TABLE AS C
 )X WHERE X.XCOL=1

    You can also use ROW_NUMBER()-based filtering