Sort Order - Include Null al last

  • sl.sajeev

    SSC Journeyman

    Points: 75

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

  • Shahnaaz Nazerali-Larsen

    Right there with Babe

    Points: 760

    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: 6173

    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: 88603

    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

  • Nick Beagley

    Ten Centuries

    Points: 1239

    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

    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

    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

    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

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

  • brewmanz

    SSCommitted

    Points: 1575

    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: 7022

    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

    SSCarpal Tunnel

    Points: 4513

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

    Please correct it please.

  • spaghettidba

    SSC Guru

    Points: 105673

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

  • Daniel van Wyk

    SSC Rookie

    Points: 37

    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

    😀

    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