• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

Special Sorting

I am having trouble figuring out how to handle the following special sort condition

Here is the sample data (MySQL DB):

salesorder , invoice_date, releasable, invoiced

1000, 2013-01-01, 0, 0
1001, 2013-01-02, 1, 0
1002, 2013-01-03, 0, 1
1003, 2013-01-04, 1, 1
1004, 2013-01-05, 0, 0


When displaying this day on the page

I need to display by:

invoice_date asc

but also the rows where releasable = 0 and invoiceable = 0 need to show before any rows that have releasable=1 or invoiced=1.


The output would need to be:


1000
1004
1001
1002
1003

Any help would be appreciated.

Thanks!
0
rjohnsonjr
Asked:
rjohnsonjr
1 Solution
 
SharathData EngineerCommented:
try this.
select *
  from your_table
 order by case when releasable = 0 and invoiceable = 0 then 0 else 1 end, invoice_date

Open in new window

0
 
Dave BaldwinFixer of ProblemsCommented:
SELECT salesorder , invoice_date, releasable, invoiced FROM yourtable ORDER BY invoiceable, releasable, invoice_date

The default order is ASC so you don't need to put that there.
0
 
Kent DyerIT Security Analyst SeniorCommented:
Is this what you are looking for?

http://sqlfiddle.com/#!3/d0d25/4

Hope this helps
0
 
SharathData EngineerCommented:
Kent, I think rjohnsonjr wants this.
http://sqlfiddle.com/#!3/d0d25/8
0
 
rjohnsonjrAuthor Commented:
Sharath_123's solution seems to work best because it maintains the date sort order on all the rows that do not have 0 in releasable and invoiced.

Here is the query:

SELECT salesorder , invoice_date, releasable, invoiced FROM test order by case when releasable = 0 and invoiced = 0 then 0 else 1 end, invoice_date

Open in new window


http://sqlfiddle.com/#!3/d0d25/9
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now