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
2
Answers
You can simply :
Or use
group by
andmin()
:First get the lowest SORT_ID of the user :
Then join it with the table to get the expected result :
Demo here
You just need to change the order by clause in your query.