Sorting SQL UNION Statements

SQL statements can get pretty complex when you are trying to prioritize the ordering of items based upon numerous levels of criteria. This can lead to SQL statements that chain together several UNION’s to ensure that the data is displayed properly. One thing you may not be aware of is that your ORDER BY directive can only be used at the end of your statement. That’s to say, you cannot issue one query and sort it, issue another query, sort it, and so on. Instead, you must wait until the very end.  To make things easier, however, you can add multiple levels of sorting. Here’s an example:

SELECT * FROM products WHERE pcategory = 'apparel' AND subcategory = 'hats'
 UNION SELECT * FROM products WHERE pcategory = 'apparel' AND subcategory = 'shirt'
 UNION SELECT * FROM products WHERE pcategory = 'apparel' AND subcategory = 'hoodie'
 UNION SELECT * FROM products WHERE pcategory = 'apparel' AND subcategory = 'jacket'
 UNION SELECT * FROM products WHERE pcategory = 'apparel' AND subcategory = 'other' ORDER BY field(subcategory,'hats','shirt','hoodie','jacket','other'), `subCatSort` ASC";
Advertisements