1

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
Nazar Hnid
  • 47
  • 4

2 Answers2

1

This is typically done using distinct on () in Postgres:

select distinct on ("User") *
from the_table
order by "User", "date";

distinct on() returns only the first row per column passed in parentheses. The "first" is determined by the order by statement of the query.

More details can be found in the manual

  • distinct ON is not working, says that "ON" is an unknown function. And even when I've deleted "ON" it never filtered it. – Nazar Hnid Sep 10 '21 at 09:05
1
 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

Sergey
  • 4,719
  • 1
  • 6
  • 11
  • Great! It worked! I always have an issue with this "Row_Number" understanding and how to use it. Do you have any manuals that I could read through? Because googling is not helping, for some reason I'm trying to read it, and makes no sense at all... – Nazar Hnid Sep 10 '21 at 09:06
  • 1
    Glad,it helps you. I am not sure if it completely works with PostgreSQL,but pretty good manual about using windows-functions is Itzik Ben-Gan's https://www.amazon.com/Microsoft-High-Performance-Functions-Developer-Reference/dp/0735658366/ref=sr_1_11?dchild=1&keywords=itzik+ben-gan&qid=1631264952&sr=8-11 – Sergey Sep 10 '21 at 09:10