Get the last entry for each row with different id's

Question

I am using 10.4.11-MariaDB.

I have the following table:

| id    | organization | reportDate | pctHeld | position   | value        | created_at          | updated_at          |
|-------|--------------|------------|---------|------------|--------------|---------------------|---------------------|
| 45829 | Org 1        | 1601424000 | 0.0204  | 346699497  | 40151268747  | 2020-11-21 01:15:18 | 2020-11-21 01:15:18 |
| 43452 | Org 2        | 1601424000 | 0.0124  | 210830547  | 24416285648  | 2020-11-20 01:13:32 | 2020-11-20 01:13:32 |
| 43450 | Org 1        | 1601424000 | 0.0204  | 346699497  | 40151268747  | 2020-11-20 01:13:32 | 2020-11-20 01:13:32 |
| 40947 | Org 1        | 1601424000 | 0.0204  | 346699497  | 40151268747  | 2020-11-19 01:04:54 | 2020-11-19 01:04:54 |
| 29211 | Org 3        | 1601424000 | 0.0098  | 166053767  | 19230686756  | 2020-11-16 00:49:26 | 2020-11-16 00:49:26 |
| 29203 | Org 2        | 1601424000 | 0.0629  | 1069771045 | 123890184721 | 2020-11-16 00:49:26 | 2020-11-16 00:49:26 |
| 26963 | Org 3        | 1601424000 | 0.0098  | 166053767  | 19230686756  | 2020-11-15 00:49:38 | 2020-11-15 00:49:38 |

I tried to get the only the last row - based on the reportDate from my table, however, I get the same result back:

select * from organization 
inner join (
    select `organization`, max(`reportDate`) as MaxDate
    from ownership
    group by `organization`
) tm on ownership.organization = tm.organization and ownership.reportDate = tm.MaxDate

Any suggestions what I am doing wrong?

I appreciate your replies!

All rows have the same `reportDate`. Which result do you want here? — GMB, Nov 21 '20 at 21:42
@jarlh I am using `Datenbank-Client Version: libmysql - mysqlnd 7.4.1` — Carol.Kar, Nov 21 '20 at 21:46
@GMB I would like to get the last row the row with the latest `updated_at` field. — Carol.Kar, Nov 21 '20 at 21:47
Your query has two tables, `organization` and `ownership`, but you are showing sample data for only one of them. That makes your question unclear. — GMB, Nov 21 '20 at 21:50

GMB · Answer 1 · 2020-11-21T22:05:09.327

1

If you want the latest row per organization, you can use window functions, which are available in MariaDB since version 10.3:

select *
from (
    select o.*, 
        row_number() over(partition by organization order by reportdate desc, updated_at desc) rn
    from ownership o
) o
where rn = 1

In earlier versions, one option uses a correlated subquery. Assuming that id is the primary key of your table:

select *
from ownership o
where id = (
    select o1.id 
    from ownership o1
    where o1.organization = o.organization
    order by o1.reportdate desc, o1.updated_at desc
    limit 1
)

edited Nov 21 '20 at 22:05

answered Nov 21 '20 at 21:53

GMB

216,147
25
84
135

Thx a lot for your reply! I just checked this with my production environment and it seems that it runs on an older version than 10.3. Is there also another way? – Carol.Kar Nov 21 '20 at 22:02
@Anna.Klee: see my update. – GMB Nov 21 '20 at 22:05

Get the last entry for each row with different id's

1 Answers1