473,325 Members | 2,816 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

SQL Query Help-- Order By Clause

Hi

I want a simple select query on a column-name (smalldatetime) with
values dislayed in desc order with null values FIRST.

i.e.

Select orderdate from orders
order by ( null values first and then orderdate in desc order)

could any one please help

Thanks
Jul 20 '05 #1
7 3562
me
Just an idea that might work somehow don't know if its a good way to do it
or not but ...
order by
isnull(cast(datefieldname as varchar(20)),'Z') desc


"Muzamil" <mu*****@hotmail.com> wrote in message
news:5a**************************@posting.google.c om...
Hi

I want a simple select query on a column-name (smalldatetime) with
values dislayed in desc order with null values FIRST.

i.e.

Select orderdate from orders
order by ( null values first and then orderdate in desc order)

could any one please help

Thanks


Jul 20 '05 #2
On 12 Aug 2004 07:09:16 -0700, Muzamil wrote:
Hi

I want a simple select query on a column-name (smalldatetime) with
values dislayed in desc order with null values FIRST.

i.e.

Select orderdate from orders
order by ( null values first and then orderdate in desc order)

could any one please help

Thanks


Hi Muzamil,

ORDER BY
CASE WHEN orderdate IS NULL THEN 1 ELSE 2 END,
orderdate DESC

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #3
>
ORDER BY
CASE WHEN orderdate IS NULL THEN 1 ELSE 2 END,
orderdate DESC

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)


IMHO - much better

declare @a table (
tid int identity(1,1) NOT NULL PRIMARY KEY
,tdate datetime NULL
)
insert into @a (
tdate
) select
'20040901'
union all
select
'20040601'
union all
select
'20040901'
union all
select
'20030101'
union all
select
NULL
union all
select
'20040212'
union all
select
NULL
select * from @a
ORDER BY
ISNULL(tdate, '20500101') desc
Jul 20 '05 #4
On Thu, 12 Aug 2004 20:02:56 +0300, Garry wrote:

ORDER BY
CASE WHEN orderdate IS NULL THEN 1 ELSE 2 END,
orderdate DESC

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)


IMHO - much better

declare @a table (
tid int identity(1,1) NOT NULL PRIMARY KEY
,tdate datetime NULL
)
insert into @a (
tdate
) select
'20040901'
union all
select
'20040601'
union all
select
'20040901'
union all
select
'20030101'
union all
select
NULL
union all
select
'20040212'
union all
select
NULL
select * from @a
ORDER BY
ISNULL(tdate, '20500101') desc


Hi Garry,

Yes you're right, that's better. Thanks. Though I'd use a date further in
the future (99991231 - the maximum datetime). Dates after Jan 1st 2050
might already start appearing in some databases (e.g. as ending date for a
mortgage or life insurance policy).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #5
Thank you, Hugo.
I did not know, that datetime type is such large.
Now I have read BOL.
Jul 20 '05 #6
Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
Yes you're right, that's better. Thanks. Though I'd use a date further in
the future (99991231 - the maximum datetime). Dates after Jan 1st 2050
might already start appearing in some databases (e.g. as ending date for a
mortgage or life insurance policy).


We went through a period where we tried to save some space by using
smalldatetime which goes over the brink in June 2076. We later reverted,
but a few tables were left behind (and an even smaller few may still
remain). And then one day as a customer of ours tried to import
securities into our database, thing blew up beacuse of a bond with a due
date in 2083 which came past this particular table.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #7
Thanks everyone for the solution as well as the valuable information

Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...
Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
Yes you're right, that's better. Thanks. Though I'd use a date further in
the future (99991231 - the maximum datetime). Dates after Jan 1st 2050
might already start appearing in some databases (e.g. as ending date for a
mortgage or life insurance policy).


We went through a period where we tried to save some space by using
smalldatetime which goes over the brink in June 2076. We later reverted,
but a few tables were left behind (and an even smaller few may still
remain). And then one day as a customer of ours tried to import
securities into our database, thing blew up beacuse of a bond with a due
date in 2083 which came past this particular table.

Jul 20 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Xenophobe | last post by:
I know this isn't a MySQL forum, but my question is related to a PHP project. I have two tables. table1 table2 "table1" contains 2 columns, ID and FirstName:
14
by: Bruce W...1 | last post by:
I do a query to MySQL using PHP. Well actually I do too many of them which is the problem, it's too slow. I think maybe an inner join or something would be better but I can't figure this out. ...
2
by: Jesse Fitterer | last post by:
This query works except when the count for the Wins and losses is 0, the query returns nothing. The count has to be at least 1 or more. ANy ideas? Select fname+' '+lname as...
2
by: aj70000 | last post by:
This is my query select ano,max(date),a_subject from MY_TAB where table_name='xyz' and ano=877 group by a_subject,ano order by a_subject ANO max(Date) A_Subject 877 2005-01-20...
4
by: frizzle | last post by:
Hi there, I can not seem to figure out where to put my WHERE clause in the next query: SELECT s.id, s.kind_id, s.active, s.name, COUNT(p.id) AS 'quantity' FROM pr_series s
1
by: Hought, Todd | last post by:
Hi all, trying to run a query against a table, to pull the date out, and order it. problem is, the date is stored in character (string) format, not as an actual timestamp, so parsing it back into...
2
by: schoultzy | last post by:
Hello Everyone, This is probably a simple fix so be kind when/if you reply. The query below retrieves information for individuals based on a column named ATTRIB_DEF, and assorted other columns;...
2
by: AJ | last post by:
Hi all, I have this monster query (at least i think it is). SELECT c.ID, c.Company_Name, p., 1 As QueryNbr FROM (Company AS c LEFT JOIN Sale AS s ON c.ID = s.Company_ID) LEFT JOIN Package...
4
by: islandfong | last post by:
Hi there, I am implementing a reporting database which manipulating a huge amount of data. I used a lot of join. Just wondering which one performs better between the two scenarios: 1....
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.