MySQL sort by 2 parameters

Question

I have a MySQL table with 2 fields:

pd_code and pd_sort (pd_sort default value=0). For each product it is possible to specify an order index (in pd_sort) -1000, -900 and so on.

So when I print out products in PHP, i would like to sort them out like this.

product1 (pd_sort = -100), product2 (pd_sort = -90) etc, and then the rest products (where pd_sort = 0) sorted by pd_code.

ORDER BY pd_sort,pd_code works only for 2 products.

Any suggestions?

Chris

Answer 1

If I understand right, you should try something like this:

SELECT * FROM table
WHERE pd_sort <> 0
ORDER BY pd_sort

UNION

SELECT * FROM table
WHERE pd_sort = 0
ORDER BY pd_code

Answer 2

A union as jab suggested should be fairly efficient, even if it does result in two queries rather than one.

If you don't want to do the union for whatever reason, another approach is to have the select generate a column by manipulating the pd_code and pd_sort values, and sort on that column. You haven't given us sample data to work with (well, other than a couple of pd_sort values), but in most cases it's possible to manipulate the data such that you end up with a sortable value, usually just by doing concats or numeric expressions. But in the most complex cases, you can fall back on case statements.