sql order by - asc and desc in same query

phillystyle123
phillystyle123 used Ask the Experts™
on
I want to list a record 1st if it's "order_id" is 1, (then 2, 3, etc.). If an "order_id" isn't specified I want to list records by the "date_added" desc (so most recently added 1st), and then the default would be the primary key asc (prod_id).  In a related issue, if the only order by conditional i user is "order_id asc", all of the records that don't have order_id are are showing up first -in other words, the records with assigned order_ids (1, 2,3) are showing up last. I tried making the default for "order_id" NULL, then I tried "0" and now it's blank. Please let me know what I'm doing wrong here - thanks:

SELECT prod_id, order_id, prodtype, prod_title, prod_desc, prod_price, prod2_title, prod2_desc, prod2_price, prod_image FROM products WHERE prodtype=7 ORDER BY order_id asc, date_added desc, prod_id asc
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
SELECT prod_id, order_id, prodtype, prod_title, prod_desc, prod_price, prod2_title, prod2_desc, prod2_price, prod_image FROM products WHERE prodtype=7
ORDER BY case when order_id is not null then 0 else 1 end ASC,
order_id asc, date_added desc, prod_id asc
Greg AlexanderLead Developer

Commented:
You can use ${$a}
Greg AlexanderLead Developer

Commented:
Oops disregard that

Author

Commented:
awesoeme - works perfectly - can you break down this line for me so i understand it?

ORDER BY case when order_id is not null then 0 else 1 end ASC

thanks!
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
It creates a virtual column to order by, where

IF ORDER_ID is not null , i.e. has a value, it is given a value of 0
otherwise (else) it is given a value of 1

Sorting on this ascending gives you what you need

Author

Commented:
thanks for clarifying.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial