SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-29-2008, 03:55 AM
| ||||
| ||||
 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      |
|
02-29-2008, 03:55 AM
| |||
| |||
| Re: SQL Query Help-- Order By Clause 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" <muzamil@hotmail.com> wrote in message news:5a998f78.0408120609.526d5bc4@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 |
|
02-29-2008, 03:55 AM
| |||
| |||
| Re: SQL Query Help-- Order By Clause 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) |
|
02-29-2008, 03:55 AM
| |||
| |||
| Re: SQL Query Help-- Order By Clause > > 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 |
|
02-29-2008, 03:56 AM
| |||
| |||
| Re: SQL Query Help-- Order By Clause 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) |
|
02-29-2008, 03:56 AM
| |||
| |||
| Re: SQL Query Help-- Order By Clause 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, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp |
|
02-29-2008, 03:56 AM
| ||||
| ||||
| Re: SQL Query Help-- Order By Clause Thanks everyone for the solution as well as the valuable information Erland Sommarskog <esquel@sommarskog.se> wrote in message news:<Xns9543F27D0C471Yazorman@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. |
Linear Mode
