CODEWITHSUNDEEP
mysqlsql
Frantisek
Frantisek

Reputation: 7693

Order by two columns

Imagine the following MySQL table of orders:

id | name
1  | Mike
2  | Steve
3  | Janet
4  | Juliet
5  | Mike
6  | Jane

This is my current query:

SELECT * FROM table ORDER BY id DESC

However, I'd like to "group" those by name, so that I have orders from the same person listed after one another, however, I cannot do ORDER BY name.

This is my desired output:

id | name
6  | Jane
5  | Mike
1  | Mike
4  | Juliet
3  | Janet
2  | Steve

What's the query for this output?

Upvotes: 1

Views: 140

Answers (6)

EWit
EWit

Reputation: 1994

You could also try this query if you want to have something which is more generic SQL.

SELECT id, name 
FROM ( SELECT id, name, (SELECT MAX(id) from Table1 where name=t.name) AS max_id 
     FROM Table1 AS t 
     ORDER BY max_id DESC, id DESC) as x

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

E.g.:

SELECT y.id
     , y.name  
  FROM my_table x 
  JOIN my_table y 
    ON y.name = x.name 
 GROUP 
    BY name
     , id 
 ORDER 
    BY MAX(x.id) DESC
     , id DESC;

Upvotes: 3

John Woo
John Woo

Reputation: 263723

You need to have special calculation to get their row position.

SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
            SELECT  Name, 
                    @ord := @ord + 1 ord
            FROM        
                    (
                        SELECT  MAX(ID) ID, NAME
                        FROM    TableName 
                        GROUP   BY Name
                    ) a, (SELECT @ord := 0) b
            ORDER   BY ID DESC
        ) b ON a.Name = b.Name
ORDER   BY b.ord, a.ID DESC

Upvotes: 2

Dusan Plavak
Dusan Plavak

Reputation: 4579

What about to use group by? You can group it by name and then order...

Upvotes: -1

Praveen Prasannan
Praveen Prasannan

Reputation: 7123

SELECT * FROM table1 
ORDER BY field(NAME,'Mike','Jane') desc,
`ID` desc;

exactly as you asked

Upvotes: 0

Alma Do
Alma Do

Reputation: 37365

You can do it via double ORDER BY:

SELECT * FROM t ORDER BY name ASC, id DESC

Upvotes: 1

Related Questions