CODEWITHSUNDEEP
python-3.xsqlite
ThatChrisGuy
ThatChrisGuy

Reputation: 90

Select the 2nd to latest record for each User ID in a table in Sqlite

I have a table like below from which I want to get the 2nd from latest based on mtg_date.

  id_pk      user_id        mtg_date
+------ |------------|--------------------+
|   0   | LastFirst  | 2019-01-02 1:00:00 |
|   1   | LastFirst  | 2020-01-02 1:00:00 |
|   2   | LastFirst  | 2020-01-03 1:00:00 |
|   3   | LastFirst  | 2020-01-04 1:00:00 |
|   4   | JonesBob   | 2020-02-02 1:00:00 |
|   5   | JonesBob   | 2020-03-02 1:00:00 |
|   6   | JonesBob   | 2020-04-02 1:00:00 |
|   7   | JonesTom   | 2020-07-02 1:00:00 |
|   8   | JonesTom   | 2020-07-03 1:00:00 |
|   9   | JonesTom   | 2020-09-02 1:00:00 |
+------ |------------|--------------------+

What I want to get:

  id_pk      user_id        mtg_date
+------ |------------|--------------------+
|   2   | LastFirst  | 2020-01-03 1:00:00 |
|   5   | JonesBob   | 2020-03-02 1:00:00 |
|   8   | JonesTom   | 2020-07-03 1:00:00 |
+------ |------------|--------------------+

Records are NOT sorted by user_or or mtg_date, but added when the next meeting date is scheduled.

I found this on another question but don't entirely understand it, and it is not working for me. Also, I am doing this in Python3 with Sqlite3.

            SELECT id_pk, user_id, Meeting_Date
            FROM Meetings
                    WHERE Meeting_Date IN (SELECT Meeting_Date
                    FROM Meetings AS T2
                    WHERE T2.id_pk = Meetings.id_pk
                    ORDER BY Meeting_Date DESC
                    LIMIT 3 OFFSET 1)        
                    """)

Upvotes: 0

Views: 56

Answers (1)

forpas
forpas

Reputation: 164174

You can use ROW_NUMBER() window function.

I want to get the 2nd from latest based on mtg_date

So if the date defines the order then use it like this:

select id_pk, user_id, mtg_date
from (
  select *, row_number() over (partition by user_id order by mtg_date desc, id_pk desc) rn
  from Meetings
)
where rn = 2;

See the demo.
Results:

| id_pk | user_id   | mtg_date           |
| ----- | --------- | ------------------ |
| 2     | LastFirst | 2020-01-03 1:00:00 |
| 5     | JonesBob  | 2020-03-02 1:00:00 |
| 8     | JonesTom  | 2020-07-03 1:00:00 |

Upvotes: 1

Related Questions