Weird Syntax Question (UNION) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Weird Syntax Question (UNION)

Author

Topic

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-07-19 : 05:45:23

Hi,

I have 2 fairly large tables,
and the objective was to get the highest value of a column from both tables.
Now as we all know, and as it says in BOL one can't use UNION in combination with an ORDER BY for the individual tables.
But, this does not seem to be the case if one puts the UNION inside a derived table!!!

create table a(i int primary key)
go
create table b(i int primary key)
go

insert a select 1 union select 2
insert b select 3 union select 4
go

/* !!! LEGAL !!! */
select top 1 i from
(
	select top 1 i from a order by i desc
	union
	select top 1 i from b order by i desc
) d
order by i desc


/* ILLEGAL !!! */
/* PS. Note the SQL is identical to the one in the derived query above */
select top 1 i from a order by i desc
union
select top 1 i from b order by i desc

rockmoose

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-19 : 06:05:40

quote:
Originally posted by rockmoose

Hi,

I have 2 fairly large tables,
and the objective was to get the highest value of a column from both tables.
Now as we all know, and as it says in BOL one can't use UNION in combination with an ORDER BY for the individual tables.
But, this does not seem to be the case if one puts the UNION inside a derived table!!!

create table a(i int primary key)
go
create table b(i int primary key)
go

insert a select 1 union select 2
insert b select 3 union select 4
go

/* !!! LEGAL !!! */
select top 1 i from
(
select top 1 i from a order by i desc
union
select top 1 i from b order by i desc
) d
order by i desc

/* ILLEGAL !!! */
/* PS. Note the SQL is identical to the one in the derived query above */
select top 1 i from a order by i desc
union
select top 1 i from b order by i desc

rockmoose

Hey Rockmoose,
That's a cool observation, man...seems like a good workaround for ORDER BY on individual tables in UNION !
Seems like Microsoft has planted workaround for every nasty limitation imposed by some standard.

Harsh Athalye
India.
"Nothing is Impossible"

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-19 : 06:43:45

Think it's just that the syntax checker doesn't spot the order by in the derived table.
You can also

select * from (select top 1 i from #a order by i desc) a
union
select * from (select top 1 i from #b order by i desc) a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-07-19 : 07:00:23

quote:
Originally posted by nr

Think it's just that the syntax checker doesn't spot the order by in the derived table.
You can also

select * from (select top 1 i from #a order by i desc) a
union
select * from (select top 1 i from #b order by i desc) a

I don't think so, if one looks at the execution plan it sure does heed the ORDER BY for both tables in the UNION.

The SQL you posted is exactly how I rewrote the query to make it "fool proof".
It felt too uncomfortable to have behavior that I am not 100% certain of that it is correct.

I have tried this on both 2000 and 2005. Same results.

StmtText
------------------------------------------------------------------------------------------------------
select top 1 i from
(
	select top 1 i from a order by i desc
	union
	select top 1 i from b order by i desc
) d
order by i desc


StmtText                                                                                                
------------------------------------------------------------------------------------------------------- 
  |--Top(1)
       |--Merge Join(Union)
            |--Top(1)
            |    |--Clustered Index Scan(OBJECT:([ods].[dbo].[a].[PK__a__000AF8CF]), ORDERED BACKWARD)
            |--Top(1)
                 |--Clustered Index Scan(OBJECT:([ods].[dbo].[b].[PK__b__01F34141]), ORDERED BACKWARD)



StmtText
-------------------------------------------------------------------------------------------------------

select top 1 i from
(
select * from (select top 1 i from a order by i desc) a
union
select * from (select top 1 i from b order by i desc) a
) d
order by i desc


StmtText                                                                                                
------------------------------------------------------------------------------------------------------- 
  |--Top(1)
       |--Merge Join(Union)
            |--Top(1)
            |    |--Clustered Index Scan(OBJECT:([ods].[dbo].[a].[PK__a__000AF8CF]), ORDERED BACKWARD)
            |--Top(1)
                 |--Clustered Index Scan(OBJECT:([ods].[dbo].[b].[PK__b__01F34141]), ORDERED BACKWARD)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-07-19 : 07:05:33

quote:

Hey Rockmoose,
That's a cool observation, man...seems like a good workaround for ORDER BY on individual tables in UNION !
Seems like Microsoft has planted workaround for every nasty limitation imposed by some standard.

Possibly, but I will not use the workaround.
Using undocumented features that maybe are not correct seems too shaky for me.
When I first saw it, I had to double check everything, just too see if it returned the "correct" result.
It appears to be ... !

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-19 : 07:13:56

I didn't mean that the query plan was wrong just that it didn't spot that the syntax maybe should have been considered incorrect before generating the plan.

There are a few instances where you can get oncorrect sql to be accepted - sometimes producing an incorrect result, sometimes correct.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-19 : 08:08:39

quote:
Originally posted by nr

Think it's just that the syntax checker doesn't spot the order by in the derived table.
You can also

select * from (select top 1 i from #a order by i desc) a
union
select * from (select top 1 i from #b order by i desc) a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr,
I think your statement is perfectly legal as far as union syntax is concerned since it is in the form of:

select <cols> from <table>
union
select <cols> from <table>

but I wonder how in rockmoose's statment, the syntax checker gets fooled !

Harsh Athalye
India.
"Nothing is Impossible"

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-07-19 : 09:06:20

> "There are a few instances where you can get oncorrect sql to be accepted - sometimes producing an incorrect result, sometimes correct."
Ok, got you, but I am not taking any chances

< "but I wonder how in rockmoose's statment, the syntax checker gets fooled !"
Good question!

rockmoose

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-07-19 : 11:26:18

> "but I wonder how in rockmoose's statment, the syntax checker gets fooled !"

I think it's because in nr's the order by is in the derived table which is rounded with ()
and in the moose's it isn't.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-07-21 : 00:19:19

It is fine because you can (must) have an order by in a sub query when you have a TOP clause. That has to be legal - otherwise TOP what? Take out the TOP and it will probaly stop working.
In Rockmoose's strangely legal case I recon the fact there is an outer ORDER BY effectively optimises away the inner ORDER BYs - both semantically (correct) and syntactically (incorrect).
Just a guess.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-25 : 04:59:32

Maybe it is somewhat connected to this query?

-- prepare test data
declare	@table table (s int, item int)

insert	@table
select	1, 1 union all
select	1, 2 union all
select	1, 3 union all
select	2, 2 union all
select	3, 5

-- do the work
select		t1.s s1,
		t2.s s2,
		1.0 * sum(case when t1.item = t2.item then 1 else 0 end) / count(distinct t1.item) matching
from		@table t1
inner join	@table t2 on t1.s <> t2.s
group by	t1.s,
		t2.s
order by	z.s1,
		z.s2

I have no reference to z table anywhere, but the code runs ok!

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-07-25 : 05:14:45

now THAT's weird

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2006-07-25 : 05:39:36

That is odd.

It works if you substitute anything else in the place of "z", but the s1 and s2 have to be real.

My guess is that the query optimizer looks at the .s1 first, and only checks the table names if there is more than one option for .s1.

Cool, this could be a fun way to annoy co-workers

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-07-25 : 06:05:42

Wow...this is awesome...I should start using this in my code. It will sooooo give meaning to my sig

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-25 : 06:07:45

It seems that ORDER BY is not what is used to be in the past

I will start a new topic and we will se what this leads to...
See topic [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69562[/url].

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources