CODEWITHSUNDEEP
mysqlsorting
Chris
Chris

Reputation: 23

MySQL sort by 2 parameters

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

Upvotes: 2

Views: 1044

Answers (2)

T.J. Crowder
T.J. Crowder

Reputation: 1074268

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.

Upvotes: 0

jab
jab

Reputation: 2984

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

Upvotes: 3

Related Questions