bubble a record to the top of mySQL query based on record number

How do I get a query to bubble a specific record to the top of the results based on a PK?

I've got a list of product classes based on a department Primary Key. My product has a class already associated with it but I want to provide the user to with the other classes available for that product only I want it's current class开发者_运维问答 to be the top result.

Does this make sense?

This will make row 123 be the first of the result:

SELECT ... ORDER BY (key_col = 123) DESC

If key_col is 123 the result of the comparison is 1, otherwise it is 0. If you ORDER in descending order, 1 comes first.

A more flexible solution to this is to add a weight column to your table. Depending on recordnumber, or an artificial primary key (especially one that is autogenerated on insert) will lead to problems later on, especially if you copy data between database servers.

Add a new column that allows you to specify a weight to a record, and then order by the weight column as a second column in your ORDER BY statement. The values in the weight column don't need to matter, as long as they're numeric and allow things to be ordered the way you want them.

My general approach to this sort of functionality is to use a CASE .. WHEN statement, which will work similarly to @AndreKR's answer above, but is a little more flexible in that it can be more granular than just a boolean comparison.

It'd look similar to this (this is how it'd look in MS SQL, but from looking at the documentation, it appears that it's similar in mySQL):

SELECT fields
FROM table
ORDER BY
  CASE 
    WHEN val = 123 THEN 0
    WHEN val < 200 THEN 1
    WHEN val < 400 THEN 2
    ELSE 3
  END
asc