How to use advanced sorting…

Question

How to use advanced sorting…

Rudi Fourie

SSC Enthusiast

Points: 115
More actions
June 3, 2007 at 3:06 am

#176876

Hi, I have table with an integer column. I want to sort the table by the integer column starting from 1 and show all rows who’s value in that column is null last. At the moment if you sort a table by a column it start from null ,1 , 2 ect. which I don’t want. I am using SQL Server 2005 Express

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

--===== Order by with NULL last...
     -- Sort/Select all non-null... will be first in list
 SELECT *
   FROM YourTable
  WHERE YourColumn IS NOT NULL
  ORDER BY YourColumn
  UNION ALL -----------------------
     -- Now, Select the nulls... will be last in list
 SELECT *
   FROM YourTable
  WHERE YourColumn IS NULL

...Yeah, sure... you could use ISNULL to replace NULL's with some max value... but what will happen if you ever have non-null data that has the max?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rudi Fourie SSC Enthusiast Points: 115 More actions · Answer 2

Rudi Fourie

SSC Enthusiast

Points: 115

June 3, 2007 at 9:22 am

#710497

Thanx, I will check it out.

Ian Yates SSCoach Points: 19738 More actions · Answer 3

How about

select *

from myTable

order by case when myCol is null then 1 else 0 end, myCol

?

This will order by a function that is 0 when your column is non-null and 1 when your function is null. After this ordering, the column itself is used. Jeff's solution might work (I haven't tried it) but if used within a view or a more complex query as a derived table I do not know if SQL Server guarantees such ordering since the rule in SQL, being set based, is that it returns a set of data, which has no specific order, unless specifically requested by an order by.

If the example above isn't clear, try looking at is like this

select *

from (

select *, case when myCol is null then 1 else 0 end as myColIsNull

from myTable

) myTablePlusIsNull

order by myColIsNull, myCol

This is a bit messier but may make it more clear.

Cheers

Koji Matsumura SSCrazy Points: 2241 More actions · Answer 4

I don' think you can UNION an ORDER BYed statement, right?

K. Matsumura

Rudi Fourie SSC Enthusiast Points: 115 More actions · Answer 5

Ok problem solved. First my query is a complex one where tables are joined and then merged (union) with another – so Koji is right you can’t use order by with a union statement. Ian’s post actually solved the problem – I enclosed my query in a SELECT * From (my query) Order by case when SortByColumn is null then 1 else 0 end, SortByColumn. Thanx again to all!!!

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

Nicely done, Ian... didn't know the OP was doing the kind of stuff he said in a later post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 7

Ian had a good idea, he just overwrote the value the OP wanted to sort by. So the records with the non-NULL values would be at the beginning but in no particular order. The trick is to add another column and use that as part of the sorting criteria:

select  case when myCol is null then 1 else 0 end as OrderByCol, 
        myCol, 
        OtherCol
from MyTable
order by OrderByCol, myCol;
-- OrderByCol myCol OtherCol
--     0        1    ...
--     0        2    ...
--     0        .
--     0        .
--     0      15200  ...   <-- Assume this is highest non-NULL
--     1       NULL  ...   <-- Now begin the NULL rows
-- and the rest of the NULL rows will follow

The problem with this, as written, is that the OrderByCol becomes part of the result set. If this is undesired, simply make the above query into a derived table and select only the desired column(s) from it.

If the table is very large and there is a performance issue with all the ordering, you could add OrderByCol to the table and index it.

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms