TSQL-Order by

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    raulggonzalez (7/15/2013)


    It's funny, though, that if you alias the column, both names can be used (original & alias) in the ORDER BY clause

    Try to think of the column alias as a very simple expression

    So Col1 AS X = 1 * Col1 AS X

    In queries you can sort by expressions AND by the original table columns, even if they are not included in the output. If I include the original column in the output, it should become more clear:

    SELECT a AS x -- "Computed" column

    ,a -- "Original" column

    FROM(SELECT 1 AS a) d

    ORDER BY

    x

    ,a

    [Edit: Added query comments]

    Best Regards,

    Chris Bรผttner

  • Dana Medley

    SSCertifiable

    Points: 6764

    Ezpz. Thanks for the question.



    Everything is awesome!

  • Miles Neale

    SSChampion

    Points: 13147

    Good Monday question. Nice explanation as well.

    Not all gray hairs are Dinosaurs!

  • Anipaul

    SSC-Insane

    Points: 24681

    Nice one....

  • manik_anu

    SSCrazy

    Points: 2367

    nice question.... ๐Ÿ˜€

    Manik
    You cannot get to the top by sitting on your bottom.

  • david.irvine 7489

    Ten Centuries

    Points: 1268

    The explanation says

    In T-SQL the nulls are returned at the beginning of the result set if ordering is specified. Standard SQL supports the options NULLS FIRST and NULLS LAST to control how NULLs sort, but T-SQL doesnโ€™t support this option.

    For me it's clearer to say that NULLS are treated as the lowest possible value. If you ORDER BY DESC then the NULLs appear at the end of the result set. That's not clear to me from the explanation.

    I guess it's just a question of what you mean by 'first' and 'last'. ๐Ÿ™‚

  • David Conn

    SSCertifiable

    Points: 5565

    Good Question. I've never really thought about Nulls being in a Column that I was ordering. In general the "Order by" Columns tend to the most important Columns in the Result and I wouldn't expect them to contain Null.

    David

  • bobby-robbins

    Grasshopper

    Points: 13

    but what order with _the_ null_s_ be returned? no particular order; yes, they both come first, but which before the other? who knows? can't tell.

  • sqlnaive

    SSCoach

    Points: 17435

    Pretty easy. ๐Ÿ™‚

  • SQLRNNR

    SSC Guru

    Points: 281210

    kapil_kk (7/14/2013)


    Lokesh Vij (7/14/2013)


    Easy one for Monday morning ๐Ÿ™‚

    Thanks Pramod!

    +1 ๐Ÿ™‚

    +1

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 10 posts - 16 through 25 (of 25 total)

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