Order By Clause

Question

Order By Clause

Viewing 15 posts - 31 through 45 (of 50 total)

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Nice question, but I got it wrong by the ambigous column name (not really the point the question was focusing on).

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

antony-688446 Ten Centuries Points: 1221 More actions · Answer 2

Hey guys - what am I missing?? I had 3 as failing, so was stumped that 1, 3 and 4 wasn't an option - I knew that 2 would work, as have met the ORDER BY with a TOP in sub select before.

But I CANNOT construct a query like 3 that doesn't fail with an ambiguous column error - what gives?

The following code

SELECT a.co_seq

FROM F_Pm_Tenant a

JOIN F_Contact b ON a.co_seq = b.co_seq

ORDER BY co_seq

gets the result:

Msg 209, Level 16, State 1, Line 4

Ambiguous column name 'co_seq'.

every time. What is the difference (apart from the table & column names!) between this and the example in the question?

Confused :unsure:

cengland0 SSCertifiable Points: 6102 More actions · Answer 3

antony-688446 (9/6/2011)
Hey guys - what am I missing?? I had 3 as failing, so was stumped that 1, 3 and 4 wasn't an option - I knew that 2 would work, as have met the ORDER BY with a TOP in sub select before.
But I CANNOT construct a query like 3 that doesn't fail with an ambiguous column error - what gives?
The following code
SELECT a.co_seq
FROM F_Pm_Tenant a
JOIN F_Contact b ON a.co_seq = b.co_seq
ORDER BY co_seq
gets the result:
Msg 209, Level 16, State 1, Line 4
Ambiguous column name 'co_seq'.
every time. What is the difference (apart from the table & column names!) between this and the example in the question?
Confused :unsure:

Which version of SQL Server are you using? I tested this on 2005.

antony-688446 Ten Centuries Points: 1221 More actions · Answer 4

SQL Server 2005 SP3 - 9.00.4060 (which I can't find on MS's version list 🙂 )

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

cengland0 (9/6/2011)
antony-688446 (9/6/2011)
Hey guys - what am I missing?? I had 3 as failing, so was stumped that 1, 3 and 4 wasn't an option - I knew that 2 would work, as have met the ORDER BY with a TOP in sub select before.
But I CANNOT construct a query like 3 that doesn't fail with an ambiguous column error - what gives?
The following code
SELECT a.co_seq
FROM F_Pm_Tenant a
JOIN F_Contact b ON a.co_seq = b.co_seq
ORDER BY co_seq
gets the result:
Msg 209, Level 16, State 1, Line 4
Ambiguous column name 'co_seq'.
every time. What is the difference (apart from the table & column names!) between this and the example in the question?
Confused :unsure:
Which version of SQL Server are you using? I tested this on 2005.

3 should fail as well for the very reason stated - it is ambiguous.

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

antony-688446 Ten Centuries Points: 1221 More actions · Answer 6

SQLRNNR (9/6/2011)
cengland0 (9/6/2011)
antony-688446 (9/6/2011)
Hey guys - what am I missing?? I had 3 as failing, so was stumped that 1, 3 and 4 wasn't an option - I knew that 2 would work, as have met the ORDER BY with a TOP in sub select before.
But I CANNOT construct a query like 3 that doesn't fail with an ambiguous column error - what gives?
The following code
SELECT a.co_seq
FROM F_Pm_Tenant a
JOIN F_Contact b ON a.co_seq = b.co_seq
ORDER BY co_seq
gets the result:
Msg 209, Level 16, State 1, Line 4
Ambiguous column name 'co_seq'.
every time. What is the difference (apart from the table & column names!) between this and the example in the question?
Confused :unsure:
Which version of SQL Server are you using? I tested this on 2005.
3 should fail as well for the very reason stated - it is ambiguous.

Which would mean that the question and BOL are wrong...

antony-688446 Ten Centuries Points: 1221 More actions · Answer 7

Ok, own question answered - compatibility level was set to SQL Server 2000 (80) (Don't ask me why, as I don't know...). Changed to 2005, and the query now executes as per BOL.

Nice to finally get an answer!

And great question, as I've finally found something that compatibility levels affect...

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

antony-688446 (9/6/2011)
Ok, own question answered - compatibility level was set to SQL Server 2000 (80) (Don't ask me why, as I don't know...). Changed to 2005, and the query now executes as per BOL.
Nice to finally get an answer!
And great question, as I've finally found something that compatibility levels affect...

I was about to answer with a test setup that I created.

IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'tab1' AND type = 'u')

BEGIN

DROP TABLE Tab1

END

GO

IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'tab2' AND type = 'u')

BEGIN

DROP TABLE Tab2

END

GO

SELECT TOP 10000

OrderID = IDENTITY(INT,1,1),

OrderAmt = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),

OrderDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME)

INTO dbo.tab1

FROM Master.dbo.SysColumns t1,

Master.dbo.SysColumns t2

go

CREATE TABLE [dbo].tab2(

[OrderID] [int] NOT NULL,

[OrderDetailID] [int] NOT NULL,

[PartAmt] [money] NULL,

[PartID] [int] NULL)

;

Insert Into tab2 (OrderID,OrderDetailID,PartAmt,PartID)

Select OrderID,

OrderDetailID = 1,

PartAmt = OrderAmt / 2,

PartID = ABS(CHECKSUM(NEWID()))%1000+1

FROM tab1

SELECT a.OrderID

FROM dbo.tab1 a

INNER JOIN dbo.tab2 b

ON a.OrderID = b.OrderID

ORDER BY OrderID

DROP TABLE tab1

DROP TABLE tab2

SQL 2005 automatically uses the column in the select for the order by in the case we specify just this one column.

I had been caught by the ambiguous column several times - but compat mode must have been at play there.

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

Toreador SSChampion Points: 11365 More actions · Answer 9

cengland0 (9/6/2011)

Tee Time (9/6/2011)
The most commonly selected answer (so far) is "Query 3 and Query 4 will fail" (28%) which means many people think it's okay to put an order by clause inside a subquery.

I selected that option, as I didn't realise that ordering by an unqualified column would use the one specified in the Select.

I found the question difficult as a lesson I learned many years ago is always to qualify everything, both for clarity, and to prevent unexpected results.

SQLWinther SSCertifiable Points: 6294 More actions · Answer 10

Thanks for the excellent question, I really had to think.

#4 was obvious due to the SELECT *, but It took a while before I realized that you need a TOP clause in the sub select.

BTW, SELECT * is one of the top mistakes that you should avoid, and this is one of the reasons, but there are plenty of more.

/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform

skrilla99 SSCertifiable Points: 5795 More actions · Answer 11

skrilla99

SSCertifiable

Points: 5795

September 7, 2011 at 11:20 am

#1378162

Thanks for the question!

-Dan

TomThomson SSC Guru Points: 104773 More actions · Answer 12

Good question.

I got it wrong. Q1 is obviously wroing, and I immediately concluded it was all about "order by" and just skim the rest for "order by" related errors - so missed the obvious problem with 4. Another confirmation that one should read code carefully, not skim it carelessly.

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 13

But of course one could say that the query processing ought to notice that there is no ambiguity in number 4, because any possible ambiguity is eliminated by the ON clause of the join. Would such ambiguity elimination be a good thing or a bad thing? I'm in two minds about it.

Tom

cengland0 SSCertifiable Points: 6102 More actions · Answer 14

Tom.Thomson (9/7/2011)
But of course one could say that the query processing ought to notice that there is no ambiguity in number 4, because any possible ambiguity is eliminated by the ON clause of the join. Would such ambiguity elimination be a good thing or a bad thing? I'm in two minds about it.

Because the two values are equal, I agree that it's not technical ambiguity; however, you can use nonequalities in the joins too like:

ON Column1 > Column2

Shouldn't happen often but the complexities in the query optimizer would be too difficult for Microsoft to handle 😀

And to make it clear, it was due to the ORDER BY clause so the subject of the QOTD was still okay. If you were to remove the ORDER BY clauses, all 4 queries would run properly. Query 1 and 4 fail because of the invalid ORDER BY clauses -- not because of any other reason.

CoolCodeShare SSCrazy Points: 2584 More actions · Answer 15

CoolCodeShare

SSCrazy

Points: 2584

September 7, 2011 at 10:38 pm

#1378357

Thanks for the nice questions!!