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 7 3562
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
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)
> 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
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)
Thank you, Hugo.
I did not know, that datetime type is such large.
Now I have read BOL.
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
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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:
|
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.
...
|
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...
|
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...
|
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
|
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...
|
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;...
|
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...
|
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....
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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...
|
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...
| |