CODEWITHSUNDEEP
mysql
abiku
abiku

Reputation: 2246

Sorting connected records in mysql table

Have Users table, where users can have multiple accounts. Table can look like this:

u_id | u_parent_d | date_added

1    | 1          | 2017-01-01
2    | 2          | 2017-01-04
3    | 1          | 2017-01-05
4    | 4          | 2017-01-06
5    | 2          | 2017-01-07

How can I order these records by date added but grouped connected accounts together

u_id | u_parent_d | date_added

5    | 2          | 2017-01-07
2    | 2          | 2017-01-04
4    | 4          | 2017-01-06
3    | 1          | 2017-01-05
1    | 1          | 2017-01-01

Upvotes: 1

Views: 47

Answers (2)

Stefano Zanini
Stefano Zanini

Reputation: 5926

You can build your query in two steps. First of all get the maximum date for each u_parent_d

select   u_parent_d, max(date_added) as max_date
from     Users
group by u_parent_d

Then you can join this with the initial table, and use max_date for sorting

select  t1.*
from    Users t1
join    (
            select   u_parent_d, max(date_added) as max_date
            from     Users
            group by u_parent_d
        ) t2
on      t1.u_parent_d = t2.u_parent_d
order by t2.max_date desc, t1.date_added desc

Upvotes: 3

Maxim
Maxim

Reputation: 243

Order both by date and parent id:

SELECT * FROM users
ORDER BY u_parent_id, date_added DESC

Upvotes: -1

Related Questions