Sort Order - Include Null al last

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Sajeev SL » Sort Order - Include Null al last

139 posts, Page 1 of 14

1 2 3 4 5 »»»

Sort Order - Include Null al last

Rate Topic

Display Mode

Topic Options

Author

Message

sl.sajeev

Posted Saturday, June 21, 2008 11:12 AM

SSC 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

Shahnaaz Nazerali-Larsen

Posted Saturday, June 21, 2008 2:23 PM

Right there with Babe

Group: General Forum Members
Last Login: Friday, June 27, 2008 2:35 AM
Points: 732, Visits: 16

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

Tom Garth

Posted Saturday, June 21, 2008 2:37 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, February 04, 2011 7:20 AM
Points: 977, Visits: 1,499

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

That's just wrong. Hehe

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

Jeffrey Williams 3188

Posted Sunday, June 22, 2008 5:50 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, June 10, 2013 12:13 PM
Points: 4,319, Visits: 9,217

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

Nick Beagley

Posted Sunday, June 22, 2008 8:14 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, November 25, 2012 3:50 PM
Points: 587, Visits: 251

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

Iggy-SQL

Posted Sunday, June 22, 2008 10:02 PM

SSCrazy

Group: General Forum Members
Last Login: Sunday, June 16, 2013 3:40 PM
Points: 2,883, Visits: 411

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

antony-688446

Posted Sunday, June 22, 2008 10:14 PM

SSC Veteran

Group: General Forum Members
Last Login: Sunday, May 26, 2013 9:33 PM
Points: 255, Visits: 278

I also picked option 2, only to be told I was wrong, and should have picked option 2! Crying

Post #521507

Iggy-SQL

Posted Sunday, June 22, 2008 10:45 PM

SSCrazy

Group: General Forum Members
Last Login: Sunday, June 16, 2013 3:40 PM
Points: 2,883, Visits: 411

antony (6/22/2008)

I also picked option 2, only to be told I was wrong, and should have picked option 2! Crying

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

Brian Pedaci

Posted Sunday, June 22, 2008 10:51 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 20, 2013 10:45 AM
Points: 19, Visits: 100

Glad I wasn't the only one who thought the answer was wrong.

Post #521515

brewmanz

Posted Monday, June 23, 2008 12:23 AM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 04, 2012 11:16 AM
Points: 477, Visits: 389

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 »

139 posts, Page 1 of 14

1 2 3 4 5 »»»

Permissions