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

Postgresql – Posgresql Select row with lowest value for specific column

i have the following table :

ID     SORT_ID     MY_DATE       USER_ID
10       1         01/01/2023      1
11       2         01/09/2023      1
13       3         15/08/2023      1
14       4         10/12/2023      1

i want to select (for the USER_ID = 1) the date of the row with the lowest SORT_ID which is the row with SORT_ID = 1 and ID = 10

i tried this query but it returns the highest (row with : SORT_ID = 4 and ID = 14 )

SELECT DISTINCT ON (USER_ID)
MY_DATE 
FROM MY_TABLE where USER_ID = 1
ORDER BY USER_ID;

do you have any idea to adjust my select in order to get the lowest row (USER_ID = 1 AND SORT_ID = 1 AND ID = 10 ) dynamically

Tags:

2

Answers


  1. 0

    You can simply :

    SELECT *
FROM MY_TABLE 
WHERE USER_ID = 1
ORDER BY SORT_ID
LIMIT 1

    Or use group by and min() :

    First get the lowest SORT_ID of the user :

    select USER_ID, min(SORT_ID) as min_id
from MY_TABLE
where USER_ID = 1
group by USER_ID

    Then join it with the table to get the expected result :

    select t.*
from MY_TABLE t
inner join (
  select USER_ID, min(SORT_ID) as min_SORT_ID
  from MY_TABLE
  where USER_ID = 1
  group by USER_ID
) as s on s.USER_ID = t.USER_ID and s.min_SORT_ID = t.SORT_ID
where t.USER_ID = 1

    Demo here

    Login or Signup to reply.
  2. 0

    You just need to change the order by clause in your query.

    SELECT DISTINCT ON (USER_ID)
MY_DATE 
FROM MY_TABLE where USER_ID = 1
ORDER BY USER_ID,SORT_ID;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search