Ordering by Alias

Question

Post reply

Ordering by Alias

Steve Jones - SSC Editor

SSC Guru

Points: 736212
More actions
October 25, 2019 at 12:00 am

#3685521

Comments posted to this topic are about the item Ordering by Alias

Viewing 13 posts - 1 through 12 (of 12 total)

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

DesNorton SSC-Insane Points: 24386 More actions · Answer 1

There is a typo in the explanation. The date format is given for 103 and not 101

How to post data/code on a forum to get the best help.

Stewart "Arturius" Campbell SSC Guru Points: 72625 More actions · Answer 2

Nice question, thanks Steve

However, the answer reflects 103's explanation, not 101

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Toreador SSChampion Points: 11365 More actions · Answer 3

The answer depends on the data type of dbo.SalesHeader.OrderDate. If it's a datetime or similar then the answer is 6 as given. But if it's a varchar column then the answer is 7.

david.edwards 76768 Ten Centuries Points: 1246 More actions · Answer 4

Ah, I'm not the only one to notice the typo then 🙂

"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?

Thom A SSC Guru Points: 99130 More actions · Answer 5

Toreador wrote:

The answer depends on the data type of dbo.SalesHeader.OrderDate. If it's a datetime or similar then the answer is 6 as given. But if it's a varchar column then the answer is 7.

However, if it were a varchar, why would you be converting it to a varchar..? Style codes only affected when convert to or from a varchar from another data type. Although not explicitly stated that OrderDate is a Date and time datatype, it's well implied.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Toreador SSChampion Points: 11365 More actions · Answer 6

Thom A wrote:

However, if it were a varchar, why would you be converting it to a varchar..? Style codes only affected when convert to or from a varchar from another data type. Although not explicitly stated that OrderDate is a Date and time datatype, it's well implied.

Indeed. Also if it were a varchar then why would you call it OrderDate? But I've seen worse designs over the years!

jschmidt 17654 SSCommitted Points: 1656 More actions · Answer 7

Did the answer change from something else to 6? I'm seeing OrderKey 6 as correct now, and that seems to work.

With date type for original order date, format 101 returns OrderKey 6 and OrderDate 02/09/2015. Format 103 returns OrderKey 1 and OrderDate 01/09/2017.

Thom A SSC Guru Points: 99130 More actions · Answer 8

jschmidt 17654 wrote:

Did the answer change from something else to 6?

Nope, it was always 6.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Alan Burstein SSC Guru Points: 61141 More actions · Answer 9

Good one Steve!

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

john-byrne SSCrazy Points: 2322 More actions · Answer 10

date format 101 is mm/dd/yyy and therefore the answer is 4

Thom A SSC Guru Points: 99130 More actions · Answer 11

john-byrne wrote:

date format 101 is mm/dd/yyy and therefore the answer is 4

'04'is less than '02'?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 12

In 101, I get this returned:

2019-10-28 08_32_08-Window

The formatting returns mm/dd/yyyy, which means we are ordering by month first, then day, then year. For a character set, "02" is the earliest date, which is orderkey 6.

Explanation corrected.