CODEWITHSUNDEEP
mysqlsql-order-by
jamboiv
jamboiv

Reputation: 13

MySQL: Order By Specific Fields

First question ever so be harsh :)

I am needing to sort by a specific department type or department(s) followed by rank. The default setting orders by Rank, Dept_Type and Department. The front end is PHP but I'm looking more for a SQL fix if possible. I also have a source table of departments if a sub query could work.

Orgin Table:

+-----------+------------+------+
| Dept_Type | Department | Rank |
+-----------+------------+------+
| Inbound   | Dept R     |   1  |
| Outbound  | Dept P     |   2  |
| Inbound   | Dept E     |   3  |
| Outbound  | Dept D     |   4  |
| Outbound  | Dept D     |   5  |
| Outbound  | Dept D     |   6  |
+-----------+------------+------+

I need to specify to order by "Dept D", "Dept E", then everything else like so:

SELECT * 
FROM table 
ORDER BY FIELD(department, "Dept D", "Dept E"), Rank, Dept_Type, Department

What I want it to return (DDDERP):

+-----------+------------+------+
| Dept_Type | Department | Rank |
+-----------+------------+------+
| Outbound  | Dept D     |   4  |
| Outbound  | Dept D     |   5  |
| Outbound  | Dept D     |   6  |
| Inbound   | Dept E     |   3  |
| Inbound   | Dept R     |   1  |
| Outbound  | Dept P     |   2  |
+-----------+------------+------+

What it actually returns(RPDDDE):

+-----------+------------+------+
| Dept_Type | Department | Rank |
+-----------+------------+------+
| Inbound   | Dept R     |   1  |
| Outbound  | Dept P     |   2  |
| Outbound  | Dept D     |   4  |
| Outbound  | Dept D     |   5  |
| Outbound  | Dept D     |   6  |
| Inbound   | Dept E     |   3  |
+-----------+------------+------+

So in theory I want:

ORDER BY FIELD("Dept D", "Dept E", department), Rank

But it obviously does not work that way. Thank you for taking time to look at it.

Upvotes: 1

Views: 51

Answers (1)

AgRizzo
AgRizzo

Reputation: 5271

Use the DESC attribute:

ORDER BY FIELD(department, 'Dept E', 'Dept D') DESC, Rank, Dept_Type, Department

Upvotes: 1

Related Questions