Sort Order - Include Null al last

Question

Post reply

Sort Order - Include Null al last

sl.sajeev

SSC Journeyman

Points: 75
More actions
June 21, 2008 at 11:12 am

#193084

Comments posted to this topic are about the item Sort Order - Include Null al last

Viewing 15 posts - 1 through 15 (of 138 total)

You must be logged in to reply to this topic. Login to reply

Shahnaaz Nazerali-Larsen Right there with Babe Points: 760 More actions · Answer 1

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.

Tom Garth SSCertifiable Points: 6193 More actions · Answer 2

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[/url]

"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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Nick Beagley Ten Centuries Points: 1349 More actions · Answer 4

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..

Iggy-SQL SSCarpal Tunnel Points: 4169 More actions · Answer 5

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

antony-688446 Ten Centuries Points: 1221 More actions · Answer 6

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

Iggy-SQL SSCarpal Tunnel Points: 4169 More actions · Answer 7

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

Brian Pedaci Valued Member Points: 69 More actions · Answer 8

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

brewmanz SSCommitted Points: 1575 More actions · Answer 9

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'?

Manie Verster SSCertifiable Points: 7152 More actions · Answer 10

As far as I know, if you use isnull then the field, when null will take the value presented in the isnull, that means that question 2 must be right. Heck, can 98% people have it wrong. Shucks.........

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

Asim Arif SSCertifiable Points: 5306 More actions · Answer 11

I made the right choice that is #2 but it says that I'm wrong??

Please correct it please.

spaghettidba SSC Guru Points: 105732 More actions · Answer 12

Really funny! I've been a member since 2002 and I've never answered qod before: I answered two questions in the last ten days and both were wrong!

There's something wrong with me, maybe...

-- Gianluca Sartori

Daniel van Wyk SSC Rookie Points: 37 More actions · Answer 13

I also chose option 2 and was told that it's not correct.

It seems that the "answer" is both options 2 and 3, which is incorrect. option 3 does not result in the sample output given the sample input.

According to the explanation of the answer only option 2 is correct.

This is my first time answering a question of the day, but clearly there seems to be something wrong here. What's going on with this question?

Biggles-581128 Old Hand Points: 303 More actions · Answer 14

😀

Seems the author fell into the trap the question was trying to highlight. Only option 2 gives the required result!!