Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Sort Order - Include Null al last Expand / Collapse
Author
Message
Posted Saturday, June 21, 2008 11:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 26, 2010 9:26 AM
Points: 29, Visits: 15
Comments posted to this topic are about the item Sort Order - Include Null al last
Post #521276
Posted Saturday, June 21, 2008 2:23 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, June 12, 2014 11:48 AM
Points: 732, Visits: 19
The statement :
Select EmpName, DateOfLeaving from Employees order by DateOfLeaving desc, EmpName asc

will not return the date in ascending order as shown by the result set in the question!!

So there is only one correct answer to this question.
Post #521319
Posted Saturday, June 21, 2008 2:37 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
Select EmpName, DateOfLeaving from Employees order by DateOfLeaving desc, EmpName asc


That's just wrong.

DECLARE @Employees table (
EmpName varchar(5),
DateOfLeaving datetime
)

insert into @Employees select 'Abc', '10 Oct 1999'
insert into @Employees select 'Bcd', '11 Nov 1998'
insert into @Employees select 'Ccd', NULL
insert into @Employees select 'Dcd', '10 Aug 2000'
insert into @Employees select 'Eed', NULL

Select
EmpName,
DateOfLeaving
from @Employees
order by DateOfLeaving desc, EmpName asc

Dcd 2000-08-10 00:00:00.000
Abc 1999-10-10 00:00:00.000
Bcd 1998-11-11 00:00:00.000
Ccd NULL
Eed NULL


Only the 2nd chioice
Select
EmpName,
DateOfLeaving
from @Employees
order by isnull(DateOfLeaving, '10/10/9999'),EmpName asc

will give the desired output...
Bcd 1998-11-11 00:00:00.000
Abc 1999-10-10 00:00:00.000
Dcd 2000-08-10 00:00:00.000
Ccd NULL
Eed NULL

;)


Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
Post #521322
Posted Sunday, June 22, 2008 5:50 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, October 2, 2014 12:09 PM
Points: 4,358, Visits: 9,538
Based upon how the question was stated, and the example given - only the second one is correct. The third answer does not return the result with DateOfLeaving in ascending order.

That is just wrong.


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #521472
Posted Sunday, June 22, 2008 8:14 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, March 24, 2014 11:20 PM
Points: 587, Visits: 260
i was sure i chose the 2nd one and was suprised when i got it wrong. But it said the correct answer was the one i thought i selected (the 2nd). So i pressed back in my browser and it was the 2nd one that i had ticked.

oh well..



Post #521484
Posted Sunday, June 22, 2008 10:02 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:26 AM
Points: 2,953, Visits: 439
the third answer is incorrect, because it would have put the latest date of leaving first, instead of the earliest date of leaving first. Thus it will contradict the question it self

sorted ascending order of date of leaving


oh well, if the author doesn't read this post then at least it's only 1 point gone.



Urbis, an urban transformation company
Post #521505
Posted Sunday, June 22, 2008 10:14 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 2:57 PM
Points: 271, Visits: 310
I also picked option 2, only to be told I was wrong, and should have picked option 2!
Post #521507
Posted Sunday, June 22, 2008 10:45 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:26 AM
Points: 2,953, Visits: 439
antony (6/22/2008)
I also picked option 2, only to be told I was wrong, and should have picked option 2!


I think you misread the answer. The answer according to the author is 2 and 3. It's the way it's displayed seems to be just a single answer.



Urbis, an urban transformation company
Post #521512
Posted Sunday, June 22, 2008 10:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 17, 2014 10:22 AM
Points: 19, Visits: 117
Glad I wasn't the only one who thought the answer was wrong.
Post #521515
Posted Monday, June 23, 2008 12:23 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 3:37 AM
Points: 488, Visits: 405
Can someone *PLEASE* check that the Q posers actually read what they write.
Or maybe it's me that's can't read properly. If so, can someone please explain to me how a descending date sort ('Select EmpName, DateOfLeaving from Employees order by DateOfLeaving desc, EmpName asc') can produce an ascending date order ('and all others sorted ascending order of date of leaving')
Of course, once again, the English is not very clear, and is subject to misinterpretation (as mentioned in a previous topic post), but in making it read in proper English like what I do (cf Ernie Wise for this grammatical construct), I cannot see how the 3rd answer offered can possible be correct.
Maybe the poser is confusing 'date' with 'age'?
Post #521544
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse