Order by then Group By in Mysql with Join

Question

I have two table one contains the user information while other contains the user's device token and device type etc.

tbl_user

+----+----------+-----------+
| id | username | is_active |
+----+----------+-----------+
| 15 | UserA    | 1         | 
| 62 | UserB    | 1         | 
+----+----------+------------

tbl_user_token

+----------+---------+--------------+
| table_id | user_id | device_token |
+----------+---------+--------------+
| 1        | 15      | XXXXXXXXX    |
| 2        | 15      | XXXXXXXXX    |
| 3        | 15      | XXXXXXXXX    |
| 4        | 62      | XXXXXXXXX    |
| 5        | 62      | XXXXXXXXX    |
| 6        | 62      | XXXXXXXXX    |
+----------+---------+--------------+

So now i want to fetch the latest record from tbl_user_token in above case it should be

+---+----+-----------+
| 3 | 15 | XXXXXXXXX |
| 6 | 62 | XXXXXXXXX | 
+---+----+-----------+

So far i have done below:

SELECT * FROM tbl_user tu 
join tbl_user_token tut 
on tut.user_id = tu.id  
where tu.id in (15,62) and tu.is_active=1
group by tut.user_id

But it does not fetch the latest record.

Note: My question is different then the suggested question because answers on that question focus on how to fetch max value records from single table. But there is no information about what to do if need to use join with another table and find latest records.

I have to add criteria in other table (i.e. with tbl_user) table.

No, my question is different. Because i need to add additional condition in my tbl_user table (like fetch only those users which are active etc.) — DS9, Mar 04 '17 at 08:19
No, your question is not different, since it does not describe any additional criteria you mentioned in your comment. — Shadow, Mar 04 '17 at 08:41
on your suggested question, they are finding records only with in 1 table. While i have to add additional condition with first table. like SELECT * FROM tbl_user tu join tbl_user_token tut on tut.user_id = tu.id where tu.id in (15,62) and tu.status = 1 group by tut.user_id — DS9, Mar 04 '17 at 08:44

Gurwinder Singh · Accepted Answer · 2017-03-04T08:33:36.857

1

As per your expected output, you don't need to join with tbl_user table.

You can find the latest table_id from each user_id and then join it with original table to get the required result.

Try this:

select t1.*
from tbl_user_token t1
join (
    select user_id, max(table_id) table_id
    from tbl_user_token
    where user_id in (15, 62)
    group by user_id
    ) t2 on t1.user_id = t2.user_id
    and t1.table_id = t2.table_id

Another method is using a left self join:

select t1.*
from tbl_user_token t1
left join tbl_user_token t2 on t1.user_id = t2.user_id
    and t1.table_id < t2.table_id
where t2.user_id is null
    and t1.user_id in (15, 62)

EDIT:

If you want to join the tbl_user, try this:

select t1.*, tu.*
from tbl_user_token t1
join (
    select user_id,
        max(table_id) table_id
    from tbl_user_token
    where user_id in (15, 62)
    group by user_id
    ) t2 on t1.user_id = t2.user_id
    and t1.table_id = t2.table_id
join tbl_user tu on t1.user_id = tu.id

For left join alternative, try this:

select t1.*, tu.*
from tbl_user_token t1
left join tbl_user_token t2 on t1.user_id = t2.user_id
    and t1.table_id < t2.table_id
join tbl_user tu on t1.user_id = tu.id
where t2.user_id is null
    and t1.user_id in (15, 62)

edited Mar 04 '17 at 08:33

answered Mar 04 '17 at 07:08

Gurwinder Singh

38,557
6
51
76

While I'm a great fan of the second method (it *is* called the strawberry query after all ;-) ), it scales so poorly in relation to the first that I really wouldn't bother with it anymore. – Strawberry Mar 04 '17 at 07:46
@Strawberry - Well it depends. See this post by Bill Karwin showing analysis and comparison of the two method and proving the second one is way faster. - http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group/1313293#1313293 – Gurwinder Singh Mar 04 '17 at 07:50
I'm very suspicious of that result. I'll try to replicate later, but I suspect that either there was some query caching going on or things were very different back in 2009. – Strawberry Mar 04 '17 at 08:05
@GurV, thanks for your answer. But what if i want to use join with tbl_user table as i have to add addition condition in query. – DS9 Mar 04 '17 at 08:31
@DS9 - Added an update to the answer. Please check – Gurwinder Singh Mar 04 '17 at 08:34
Thank you so much. Its worked. – DS9 Mar 04 '17 at 08:36

score 1 · Answer 2 · answered Mar 04 '17 at 07:38

You can give a row number group by user_id and in the descending order of table_id. Then select the rows with row number 1.

Query

select t1.`table_id`, t1.`user_id`, t1.`device_token` from 
(
    select `table_id`, `user_id`, `device_token`, 
    (
        case `user_id` when @A 
        then @R := @R + 1 
        else @R := 1 and @A := `user_id` end 
    ) as `rn`
    from `tbl_user_token` t, 
    (select @R := 0, @A := '') r 
    order by `user_id`, `table_id` desc 
)t1 
where t1.`rn` = 1
order by t1.`table_id`;

Sql Fiddle demo here

Order by then Group By in Mysql with Join

2 Answers2

EDIT: