Nulls last.

[ptheriault]

I want to order a query in asc order but I want the nulls to be last. in Oracle I can use (nulls last). How can I do that in T-SQL?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](Data [COLOR=blue]Int[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](2)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](NULL)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](NULL)

[COLOR=blue]Select[/color] * 
[COLOR=blue]From[/color]   @Temp
[COLOR=blue]Order[/color] [COLOR=blue]By[/color] [COLOR=blue]Case[/color] [COLOR=blue]When[/color] Data [COLOR=blue]Is[/color] NULL [COLOR=blue]Then[/color] 1 [COLOR=blue]Else[/color] 0 [COLOR=blue]End[/color], Data

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ptheriault]

Thanks George!

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

No problem. You're welcome.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TysonLPrice]

Does that have an advantage over:

Declare @Temp Table(Data Int)

Insert Into @Temp Values(1)
Insert Into @Temp Values(2)
Insert Into @Temp Values(NULL)
Insert Into @Temp Values(NULL)

Select *
From @Temp
order by data desc

Or is it just another way to skin a cat?

 

[ptheriault]

In George's example the data is ordered ascending.

1
2
null
null

Or nulls last.

In your example the data is ordered descending

2
1
null
null

That's not how I want the data ordered.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[TysonLPrice]

oops...missed that. Thanks!

 

[gmmastros]

Tyson,

There is a difference in our queries. In my query, you end up with...

[red]1[/red]
[blue]2[/blue]
NULL
NULL

With yours, you get...

[blue]2[/blue]
[red]1[/red]
NULL
NULL

Obviously, both of our queries put NULL at the bottom, but the non-null data (the 1 and 2) are sorted ascending with my query and Descending with yours.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Uh... yeah... what Paul said.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ptheriault]

Oracle offers a very nice feature in the order by clause. It works like this.

SELECT table_name, tablespace_name
FROM all_tables
ORDER BY 2, 1 NULLS LAST;

It does the same as George's example.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLDenis]

another way

Declare @Temp Table(Data Int)

Insert Into @Temp Values(1)
Insert Into @Temp Values(2)
Insert Into @Temp Values(NULL)
Insert Into @Temp Values(NULL)

Select * 
From   @Temp
Order By coalesce(data,'99999')

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ptheriault]

But Denis,
Your example won't work for dates. Which is what I'm trying to order.
[small]
unless i've missed something!
[/small]

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

although it would work if I changed all my datetimes to varchars!

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLDenis]

of course it will, watch

Declare @Temp Table(Data datetime)

Insert Into @Temp Values(getdate())
Insert Into @Temp Values('2007-10-19 09:54:03.730')
Insert Into @Temp Values('2006-10-19 09:54:03.730')
Insert Into @Temp Values('2005-10-19 09:54:03.730')
Insert Into @Temp Values('2006-10-19 09:54:03.730')
Insert Into @Temp Values('2004-10-19 09:54:03.730')
Insert Into @Temp Values(NULL)
Insert Into @Temp Values(NULL)

Select * 
From   @Temp
Order By coalesce(data,'9999-12-31 23:59:59.997')

the trick is to use the max valid value for the data type, for smalldates it would June 6, 2079



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ptheriault]

ahhh!

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLDenis]

ahhh!

These requirements don't make sense

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions