I have got a problem and I have no idea how to solve it because I’m a beginner.
I have a table like this:
| id | date | time | title |
|---|---|---|---|
| 1 | 2023-03-03 | 2023-03-03 10:00 | A |
| 1 | 2023-03-03 | 2023-03-03 10:10 | A |
| 1 | 2023-03-03 | 2023-03-03 10:20 | A |
| 1 | 2023-03-03 | 2023-03-03 10:30 | B |
| 1 | 2023-03-03 | 2023-03-03 10:40 | B |
| 1 | 2023-03-03 | 2023-03-03 10:50 | C |
| 2 | 2023-03-03 | 2023-03-03 12:00 | A |
| 2 | 2023-03-03 | 2023-03-03 12:20 | B |
The desired result is as follows.
For each title I use only minimal time and after that I want to rank them for id and date:
| id | date | time | title | rank |
|---|---|---|---|---|
| 1 | 2023-03-03 | 2023-03-03 10:00 | A | 1 |
| 1 | 2023-03-03 | 2023-03-03 10:30 | B | 2 |
| 1 | 2023-03-03 | 2023-03-03 10:50 | C | 3 |
| 2 | 2023-03-03 | 2023-03-03 12:00 | A | 1 |
| 2 | 2023-03-03 | 2023-03-03 12:20 | B | 2 |
Can someone help me with this?
Thanks!
>Solution :
If the window functions are supported by your database, you can use the function row_number() as follows:
select id, date, time, title, row_number() over (partition by id order by time) as rnk
from (
select *, row_number() over (partition by id, title order by time) as rn
from mytable
) as s
where rn = 1;