sorting SQL by text field but skipping the empty ones

Question

So here's what the table looks like

    --------
   | field1 |
   |========|
   |  1.2   |
   |  1.7   |
   |   -    |
   |   -    |
   |  1.3   |

• the dashes are actually blank fields

My current SQL statement does a simple "ORDER BY field1" which puts the blank ones on top, however I want to get the numbers on top, and put the empty ones at the bottom.

EDIT:

My order by code looks like this:

   'Get the sort requirement as per the requirement
   strSql = strSql & " ORDER BY [RequestType], [Rank], [ADRNo]"

So i want it to order by requesttype, rank, and then ADRNo. But i want the null values to come last for all three fields. So I posted one field in my orignal question, but I am really working with 3.

Answer 1

...order by iif(field1 is null, 1, 0), field1

Logic

The iif(field1 is null, 1, 0) constructs a virtual column with 1 for all null values. so you have

    -------- -------
   | field1 |       |
   |========|=======| 
   |  1.2   |   0   |
   |  1.7   |   0   |
   |   -    |   1   |
   |   -    |   1   |
   |  1.3   |   0   |

and then the sort of this virtual column takes care of putting the numbers below the nulls.

Multiple columns

If you have multiple columns that could have nulls you'd need an additional calculated column for each column. Something like

    -------- ------- -------- -------
   | field1 |   s1  | field2 |   s2  |
   |========|=======|========|=======| 
   |   2    |   0   |   -    |   1   |
   |   1    |   0   |   1    |   0   |
   |   -    |   1   |   3    |   0   |
   |   -    |   1   |   -    |   1   |
   |   3    |   0   |   2    |   0   |
    -------- ------- -------- -------

now you have 2 options.

1. you can sort each field with the null at the end. so

...order by iif(field1 is null, 1, 0), field1, iif(field2 is null, 1, 0), field2

would give you

    -------- ------- -------- -------
   | field1 | field2 |   s1  |   s2  |
   |========|========|=======|=======|
   |   1    |   1    |   0   |   0   |
   |   2    |   -    |   0   |   1   |
   |   3    |   2    |   0   |   0   |
   |   -    |   3    |   1   |   0   |
   |   -    |   -    |   1   |   1   |
    -------- ------- -------- -------

note that in field2 there is a null (row 2) before 3 because the sort for field1 (2) overrides the null sort logic (s2) for field2. the second null (row 5) in field2 comes after 3 (row 5) because of our null specific sort logic for s2.

2. you can choose to have all nulls in field2 at the end, before sorting on field1. your code would then be...

...order by iif(field1 is null, 1, 0), iif(field2 is null, 1, 0), field1, field2

which gives you

    -------- ------- -------- -------
   | field1 | field2 |   s1  |   s2  |
   |========|========|=======|=======|
   |   1    |   1    |   0   |   0   |
   |   3    |   2    |   0   |   0   |
   |   2    |   -    |   0   |   1   |
   |   -    |   3    |   1   |   0   |
   |   -    |   -    |   1   |   1   |
    -------- ------- -------- -------

Note

(Ref: Tom Gullen's comment in question) This takes care of putting only nulls at the end (empty strings, or strings with spaces will still be in the natural sort order)

Options

So.. in your case you'd be using either

...ORDER BY 
            IIF([RequestType] IS NULL, 1, 0), [RequestType], 
            IIF([Rank] IS NULL, 1, 0), [Rank], 
            IIF([ADRNo] IS NULL, 1, 0), [ADRNo]

or

...ORDER BY 
            IIF([RequestType] IS NULL, 1, 0), 
            IIF([Rank] IS NULL, 1, 0), 
            IIF([ADRNo] IS NULL, 1, 0), 
            [RequestType], 
            [Rank], 
            [ADRNo]

Answer 2

@potatopeelings and everyone else:

My order by code looks like this:

   'Get the sort requirement as per the requirement
   strSql = strSql & " ORDER BY [RequestType], [Rank], [ADRNo]"

So i want it to order by requesttype, rank, and then ADRNo. But i want the null values to come last for all three fields. So I posted one field in my orignal question, but I am really working with 3.

Answer 3

order by feild1 desc

desc will put the NULL values last in the list

Answer 4

Here is what I think @potatopeelings suggested, applied to your ORDER BY sample:

strSql = strSql & " ORDER BY IIf(RequestType Is Null, 1, 0), " & _
    "[RequestType], IIf(Rank Is Null, 1, 0), [Rank], " & _
    "IIf(ADRNo Is Null, 1, 0), [ADRNo]"

With a single column, it's straightforward to force the Nulls to sort last with the technique @potatoepeelings showed you. However, when you have 3 columns which can contain Nulls, it's not so clear how you would want them sorted.

If this isn't what you want, revise your question to add additional columns and show us how you want them sorted.

Edit: Maybe this is closer to what you want:

strSql = strSql & " ORDER BY IIf(RequestType Is Null, 1, 0), " & _
    "IIf(Rank Is Null, 1, 0), " & _
    "IIf(ADRNo Is Null, 1, 0), [RequestType], [Rank], [ADRNo]"

It all depends on how you want the Nulls in the second and third columns ordered.

Answer 5

So as you can see, I got the TYPE, and the PTY, however I want the ACR's to be also sorted. The code I used:

 'Get the sort requirement as per the requirement
strSql = strSql & " ORDER BY IIf([RequestType] = '', 1, 0), " & _
                  "[RequestType], IIf(Rank = '', 1, 0), [Rank], " & _
                  "IIf(ADRNo = '', 1, 0), [ADRNo]"

I want it to sort by Type which it did. I want it to sort by Pty which it did.

Then for ACR Numbers, I want it to sort where PTY is not empty, and then when pty is empty. so from the image, it should sort ACR's from 1.2 - 2.6. Then for the empty PTYs

Answer 6

This ORDER BY may be more efficient in terms of index usage:

  ORDER BY IsNull(Field1) DESC, Field1

IsNull() will return a Boolean, -1 for if it's Null and 0 for when it's not, so sorting in descending order will cause the non-empty ones to be at the top of the list. Then the remaining sorting would use any index on Field1.