CODEWITHSUNDEEP
phpsqlmysql
Kevin
Kevin

Reputation: 13226

Sorting by value with ORDER BY?

For clarification, are you able to use MySQL this way to sort?

ORDER BY CompanyID = XXX DESC

What I am trying to do is use one sql query to sort everything where X = Y, in this case, where CompanyID = XXX. All values where CompanyID is not XXX should come after all the results where CompanyID = XXX.

I don't want to limit my query but I do want to sort a particular company above other listings.

Upvotes: 3

Views: 8266

Answers (5)

Amy B
Amy B

Reputation: 17977

ORDER BY FIELD(CompanyID, 'google', 'apple', 'microsoft') ASC

Google = first
Microsoft = third
The rest = last

Upvotes: 12

Marcus Adams
Marcus Adams

Reputation: 53830

Your query is fine. CompanyID = xxx yields 1 on a match, and 0 otherwise. Just add the CompanyID column as the secondary order column.

ORDER BY CompanyID = XXX DESC, CompanyID ASC

This way, you get records where the CompanyID matches your constant first, then the rest are in order.

Alternatively, you could do this:

ORDER BY CompanyID <> XXX ASC, CompanyID ASC

Upvotes: 7

Daniel
Daniel

Reputation: 7172

You could probably fake it out

SELECT CompanyID, IF(CompanyID = XXX, -1, CompanyID) AS sortby
FROM companies
ORDER BY sortby DESC

Upvotes: 2

tvanfosson
tvanfosson

Reputation: 532435

You might want to try using a CASE statement in your order by expression. When the company id matches your preferred company, return 0, else return 1. Then order by company name like usual in a secondary order by column.

 order by case when CompanyID = XXX then 0 else 1 end, CompanyName

That way you have your preferred company at the top, then all of the rest order alphabetically by name (or however you want it ordered).

Upvotes: 2

Andomar
Andomar

Reputation: 238068

You can use a case in an order by, like:

order by case when CompanyID = XXX then 1 else 2 end

Upvotes: 8

Related Questions