Sort Order - Include Null al last

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

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

  • 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
  • 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 opportunities brilliantly disguised as insurmountable obstacles.

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

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

  • 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

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

  • 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

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

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

  • 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)

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

    Please correct it please.

  • 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

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

  • 😀

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

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

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