CODEWITHSUNDEEP
mysql
nashar1
nashar1

Reputation: 1087

How to order by one column and make elements with same 2nd column contino uous?

I have two columns id and resolvedId. Now i want to order the columns by id desc but want the columns with same resolvedId to appear one after other. So far tried group by but my understanding is it can only be used for aggregate functions like sum etc. Any easy way to do this in mysql? My other option would be to get them in php as seperate resultsets and then insert based on resolvedId. id is unique auto_increment and resolvedId is going to be one of ids. Sample data

id| name| resolvedId
1   nam    1
2   bam    4
3   sam    3
4   dam    4
5   ham    3
6   ram    4

Output wanted

id|  name|  resolvedId
6    ram    4
4    dam    4
2    bam    4
5    ham    3
3    sam    3
1    nam    1

Sample data 2

id| name| resolvedId
1   nam    1
2   bam    4
3   sam    3
4   dam    4
5   ham    3
6   ram    1

Output wanted

id|  name|  resolvedId
6    ram    1
1    nam    1
5    ham    3
3    sam    3
4    dam    4
2    bam    4

Upvotes: 1

Views: 57

Answers (2)

Sylvain Leroux
Sylvain Leroux

Reputation: 52000

Here is a probably adequate solution:

select tbl.* from
    ( select MAX(id) as m, resolvedId from tbl GROUP BY resolvedId) as driver
    JOIN tbl 
    ON driver.resolvedId = tbl.resolvedId 
    ORDER BY driver.m desc, tbl.id desc, driver.resolvedId;

Works with you latest examples http://sqlfiddle.com/#!2/e3a2f/1 and http://sqlfiddle.com/#!2/7fb2ce/4

Upvotes: 2

xiaofeng.li
xiaofeng.li

Reputation: 8587

To my understanding, what you are asking could not be obtained without further constraints on the data set. Suppose you have the following data:

id    resolvedId
1              4
2              3
3              4

Order it by id desc would only result in discontinued resolvedId.

Upvotes: 0

Related Questions