How to use advanced sorting…
-
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
-
--===== 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)
Intro to Tally Tables and Functions -
Thanx, I will check it out.
-
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
-
I don' think you can UNION an ORDER BYed statement, right?
K. Matsumura
-
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!!!
-
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)
Intro to Tally Tables and Functions -
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 followThe 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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply