Mysql join to group by column to remove duplicates but still return row data

Question

i am trying to group by a column and to return the column based on desc order.

table: history

id  num   last_update   status         phase
1   2     2019-09-01    in progress    dev
2   2     2019-09-01    in progress    dev
3   3     2019-09-02    complete       done
4   4     2019-09-03    complete       done
5   6     2019-09-04    complete       done
6   6     2019-01-02    in progress    qa
7   7     2019-07-02    on hold        req

example of expected output

id  num     last_update     status       phase 
2   2       2019-09-01      in progress  dev
3   3       2019-09-02      complete     done
4   4       2019-09-03      complete     done
5   6       2019-09-04      complete     done
7   7       2019-07-02      on hold      req

I tried the following query:

SELECT   num, last_update, phase
  FROM     history
  GROUP BY last_update, num, phase
    order by last_update desc

but i am getting the following results:

id  num     last_update     status          phase
    1   2       2019-09-01      in progress     dev
    3   3       2019-09-02      complete        done
    4   4       2019-09-03      complete        done
    5   6       2019-09-04      complete        done
    6   6       2019-01-02      in progress     qa
    7   7       2019-07-02      on hold         req

but it is still returning duplicate num, because last_update is unique. I just want it to return

5   6       2019-09-04      complete        done

since this is the latest record w/ the latest status / phase update.

Any help is appreciated.

Thanks

-RD

similar issue, but i think my use case has a bit more complexity, since multiple columns are considered. I am applying the same query, but still getting duplicate records back. — Rick, Nov 02 '19 at 16:04

Fahmi · Answer 1 · 2019-11-01T05:26:21.267

0

You can try below way -

SELECT   num, last_update, phase
  FROM  history h where last_update in (select max(last_update)
from history h1 where h.num=h1.num)

edited Nov 01 '19 at 05:26

answered Nov 01 '19 at 05:18

Fahmi

37,315
5
22
31

this for me is still returning duplicates such as - 5 6 2019-09-04 complete done 6 6 2019-01-02 in progress qa – Rick Nov 01 '19 at 14:08

Ed Bangga · Answer 2 · 2019-11-01T05:29:22.547

0

you can join your max(id) for each num

select t1.id, t1.num, last_update, status, phase
from history t1
inner join (
    select num, max(id) as id
    from history
    group by num) as t2 on t2.id = t1.id
order by last_update desc

if you are using mysql v8.0+, you can consider using row_number()

select t1.id, t1.num, last_update, status, phase
from history t1
inner join (
    select num, row_number() over (partition by num order by last_update desc) as rn    
    from history
    group by num) as t2 on t2.num = t1.num
where t2.rn = 1
order by last_update desc

edited Nov 01 '19 at 05:29

answered Nov 01 '19 at 05:23

Ed Bangga

12,879
4
16
30

I have not started venturing into mySql8 yet -- but the query provided for v5 looks good thus far. let me do some more testing and report back my findings. – Rick Nov 01 '19 at 14:41
Interesting, so i think using max(id) assumes the higher id values represent later_dates... we may have a use case such as: 5 10 2019-09-02 6 10 2019-09-01 the reason why is due to the source that this table gets updated from... the update date is from another system and depending on the sequence it gets added to this table, we may see a later last_update date with a 'lower' id. – Rick Nov 01 '19 at 14:44

Mysql join to group by column to remove duplicates but still return row data

2 Answers2