CODEWITHSUNDEEP
sqlsql-server
mwilson
mwilson

Reputation: 12900

SQL Query not Working on ORDER BY

I have a SQL Table that I'm trying to query and order the return. I am able to query just fine and the SQL Statement that I'm using is also working with the exception of the last ORDER BY statement that I need to execute. The sort order is as follows:

  1. Sort the Status column so that 'open' is on top, 'closed' on bottom
  2. Order the 'Flag' column so that empty (null) values are on bottom (above Status = Closed) and values on top
  3. Order the results of items 1 and 2 by the Number column

Here is an example of the raw data:

| Flag | Number | Status |
|------------------------|
| a    |    1   |  open  |
|      |    5   |  open  |
|      |    3   | closed |
| a    |    4   |  open  |
| a    |    2   | closed |

Here is what I'm going for:

| Flag | Number | Status |
|------------------------|
|  a   |    1   |  open  |
|  a   |    4   |  open  |
|      |    5   |  open  |
|  a   |    2   | closed |
|      |    3   | closed |

The query statement that I'm using is as follows:

sqlCom.CommandText = "SELECT * FROM Questions 
    WHERE Identifier = @identifier 
    AND Flag <> 'DELETED' 
    ORDER BY Status DESC
    , (CASE WHEN Flag is null THEN 1 ELSE 0 END) ASC
    , Flag DESC
    , [Number] * 1 ASC";

Now, everything works fine, but the 3rd item above (sorting by Number column) doesn't work. Any ideas why?

What I'm currently getting:

| Flag | Number | Status |
|------------------------|
|  a   |    4   |  open  | <-- Out of order. Should be below the next record
|  a   |    1   |  open  | <-- Out of order. Should be one record up
|      |    5   |  open  | <-- OK
|      |    6   |  open  | <-- OK
|      |    3   | closed | <-- OK
|  a   |    2   | closed | <-- OK

Thanks in advance for any helpful input. I have tried fiddling with the query in SSMS but no luck.

Upvotes: 0

Views: 4472

Answers (1)

shawnt00
shawnt00

Reputation: 17915

Your third sort expression is on Flag. Those values are being sorted alphabetically before the QNumber sort applies. And note that case matters in the ordering as well.

Here's how I would write it:

ORDER BY
    Status DESC, -- might be better to use a case expression
    CASE WHEN Flag IS NOT NULL THEN 0 ELSE 1 END,
    QNumber

Since your data in the examples contradicts the data in the screenshot, it's not clear whether you needed to remove the third sort column entirely or just sort by ignoring the case of the text.

Upvotes: 3

Related Questions