TSQL-Order by

Question

TSQL-Order by

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

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

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 1

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 More actions · Answer 2

Ezpz. Thanks for the question.

Everything is awesome!

Miles Neale SSChampion Points: 13147 More actions · Answer 3

Good Monday question. Nice explanation as well.

Not all gray hairs are Dinosaurs!

Anipaul SSC-Insane Points: 24681 More actions · Answer 4

Anipaul

SSC-Insane

Points: 24681

July 16, 2013 at 5:12 am

#1632814

Nice one....

manik_anu SSCrazy Points: 2377 More actions · Answer 5

nice question.... 😀

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

david.irvine 7489 Ten Centuries Points: 1290 More actions · Answer 6

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: 6010 More actions · Answer 7

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 More actions · Answer 8

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: 17437 More actions · Answer 9

sqlnaive

SSCoach

Points: 17437

July 24, 2013 at 7:32 am

#1635346

Pretty easy. 🙂

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

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