Order by question

How do a construct a select statement that will perform an 'order by ASC',
but will place my nulls at the end of the list instead of the beginning of
the list.
Thanks.
Archerbagman3rd wrote on Wed, 11 May 2005 08:24:01 -0700:

> How do a construct a select statement that will perform an 'order by ASC',
> but will place my nulls at the end of the list instead of the beginning of
> the list.
What's the largest value you expect to see in the column? Place something
larger in the following to replace <<value to use>>
ORDER BY COALESCE(<column>,<<value to use>> )
eg. if column A will have a max value of 100, you would use
ORDER BY COALESCE(A,101)
Dan|||Figure out the "highest" possible value that can be in that column, dependin
g
on what datatype the column is, say it's varchar(6), then it would be
'ZZZZZZ', if it's a smallInt then its 32768, etc.
Then use the Coalesce function
Order By Coalesce(ColNAme, <MaxColValue> )
"bagman3rd" wrote:

> How do a construct a select statement that will perform an 'order by ASC',
> but will place my nulls at the end of the list instead of the beginning of
> the list.
> Thanks.
> Archer|||Use a CASE expression in the ORDER BY clause.
Example:
use northwind
go
select
OrderID,
ShippedDate
from
dbo.orders
order by
case when ShippedDate is null then 1 else 0 end asc,
ShippedDate asc
AMB
"bagman3rd" wrote:

> How do a construct a select statement that will perform an 'order by ASC',
> but will place my nulls at the end of the list instead of the beginning of
> the list.
> Thanks.
> Archer

ORDER BY parameter

How can I write that SORT depends on parameter, something like:
ORDER BY column1 case when @.param=0 then DESC else ASC end
Regards,SStandard SQL-92 does not allow you to use a function or expression in
an ORDER BY clause. The ORDER BY clause is part of a cursor and it can
only see the column names that appear in the SELECT clause list that
was used to build the result set. BP will now chime in that SQL-99
(officially called "a standard in progress" and not recognized by the
U.S. Government for actual use) does allow this.
But aside from this, there is the good programming practice of showing
the fields that are used for the sort to the user, usually on the left
side of each line since we read left to right.
The standard trick for picking a sorting order at run time is to use a
flag in CASE expression. If you want to sort on more than one column
and allow all possible combinations of sorting use one CASE per column:
SELECT
CASE @.flag_1
WHEN 'a' THEN CAST (a AS CHAR(n))
WHEN 'b' THEN CAST (b AS CHAR(n))
WHEN 'c' THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1,
CASE @.flag_2
WHEN 'x' THEN CAST (x AS CHAR(n))
WHEN 'y' THEN CAST (y AS CHAR(n))
WHEN 'z' THEN CAST (z AS CHAR(n))
ELSE NULL END AS sort_2,
..
CASE @.flag_n
WHEN 'n1' THEN CAST (n1 AS CHAR(n))
WHEN 'n2' THEN CAST (n2 AS CHAR(n))
WHEN 'n3' THEN CAST (n3 AS CHAR(n))
ELSE NULL END AS sort_2,
FROM Foobar
WHERE ...
ORDER BY sort_1, sort_2, ...
More than one sort column and only a limited set of combinations then
use concatenation.
CASE @.flag_1
WHEN 'ab'
THEN CAST(a AS CHAR(n)) ||' ' || CAST(b AS CHAR(n))
WHEN 'ba'
THEN CAST(b AS CHAR(n)) ||' ' || CAST(a AS CHAR(n))
ELSE NULL END AS sort_1,
If you need ASC and DESC options, then use a combination of CASE and
ORDER BY
CASE @.flag_1
WHEN @.flag_1 = 'a' AND @.flag_1_ad = 'ASC'
THEN CAST (a AS CHAR(n))
WHEN @.flag_1 = 'b' AND @.flag_1_ad = 'ASC'
THEN CAST (b AS CHAR(n))
WHEN @.flag_1 = 'c' AND @.flag_1_ad = 'ASC'
THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1_a,
CASE @.flag_1
WHEN @.flag_1 = 'a' AND @.flag_1_ad = 'DESC'
THEN CAST (a AS CHAR(n))
WHEN @.flag_1 = 'b' AND @.flag_1_ad = 'DESC'
THEN CAST (b AS CHAR(n))
WHEN @.flag_1 = 'c' AND @.flag_1_ad = 'DESC'
THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1_d
. ORDER BY sort_1_a ASC, sort_1_d DESC
I have shown explicit CAST(<exp> AS CHAR(n)), but if the datatypes of
the THEN clause expressions were already the same, there would be no
reason to force the conversions.
You change the ELSE NULL clause to any constant of the appropriate
datatype, but it should be something useful to the reader.
A neater way of doing this is to use one column for each sorting option
so you do not have worry about CAST() operations.
SELECT ...
CASE WHEN @.flag = 'a' THEN a ELSE NULL END AS sort1,
CASE WHEN @.flag = 'b' THEN b ELSE NULL END AS sort2,
CASE WHEN @.flag = 'c' THEN c ELSE NULL END AS sort3
FROM Foobar
WHERE ...
ORDER BY sort1, sort2, sort3;|||hi,
probably this link should answer your question:
http://www.aspfaq.com/2501
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"simon" wrote:

> How can I write that SORT depends on parameter, something like:
> ORDER BY column1 case when @.param=0 then DESC else ASC end
> Regards,S
>
>sql

ORDER BY ASC with NULL at the end

Hi,
How can I do an ORDER BY so the records with NULL will be at the end?
normally the order is like this: "NULL, A, B, C", but I woudl want to have
"A, B, C, NULL".
Thanks a lot in advance!
Pieterorder by case when col1 is null then 1 else 0 end, col1|||Alexander Kuznetsov wrote:
>order by case when col1 is null then 1 else 0 end, col1
or just use the isnull() function in the select and make null values be show
n
as whatever u want that will be at the end of the list?

ORDER BY ASC with NULL at the end

Hi,
How can I do an ORDER BY so the records with NULL will be at the end?
normally the order is like this: "NULL, A, B, C", but I woudl want to have
"A, B, C, NULL".
Thanks a lot in advance!
Pieter
order by case when col1 is null then 1 else 0 end, col1
|||Alexander Kuznetsov wrote:
>order by case when col1 is null then 1 else 0 end, col1
or just use the isnull() function in the select and make null values be shown
as whatever u want that will be at the end of the list?
sql

ORDER BY ASC with NULL at the end

Hi,
How can I do an ORDER BY so the records with NULL will be at the end?
normally the order is like this: "NULL, A, B, C", but I woudl want to have
"A, B, C, NULL".
Thanks a lot in advance!
Pieterorder by case when col1 is null then 1 else 0 end, col1|||Alexander Kuznetsov wrote:
>order by case when col1 is null then 1 else 0 end, col1
or just use the isnull() function in the select and make null values be shown
as whatever u want that will be at the end of the list?