UNION with ORDER BY - 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

Transact-SQL (2000)

UNION with ORDER BY

Author

Topic

Kenny Blankenship
Starting Member

25 Posts

Posted - 2004-06-11 : 09:04:11

I have, what I thought was, a simple query. I need to find the TOP records within several categories and return them as a single recordset

SELECT TOP 1 a.CellIndex, a.RSID, a.SKU, b.Description
FROM tblReleaseSequencing a
INNER JOIN tblProductList b ON b.SKU=a.SKU WHERE a.CellIndex = 1 -- ORDER BY a.RSID

UNION

SELECT TOP 1 c.CellIndex, c.RSID, c.SKU, d.Description
FROM tblReleaseSequencing c
INNER JOIN tblProductList d ON d.SKU=c.SKU WHERE c.CellIndex = 2 -- ORDER BY c.RSID

ORDER BY CellIndex

This works fine except that I would like to include an ORDER BY clause with each select statement. When I do so, the query bombs. I can only order the result, not the individual selects.

Any suggestions? Is there a better way to write this?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-06-11 : 09:14:52

This can be achieved by using derived tables:

SELECT *
FROM (SELECT TOP 1 a.CellIndex, a.RSID, a.SKU, b.Description
FROM tblReleaseSequencing a
INNER JOIN tblProductList b ON b.SKU=a.SKU WHERE a.CellIndex = 1 ORDER BY a.RSID) as a

UNION

SELECT *
FROM ( SELECT TOP 1 c.CellIndex, c.RSID, c.SKU, d.Description
FROM tblReleaseSequencing c
INNER JOIN tblProductList d ON d.SKU=c.SKU WHERE c.CellIndex = 2 ORDER BY c.RSID) as b

ORDER BY CellIndex

Duane.

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-06-11 : 09:17:22

What is the criteria on which you want the TOP 1 ? Assuming you could use the ORDER BY clauses, the way you have it written there it would take the one with the lowest RSID value.
Can't really suggest more without knowing schema and data.
Some questions..
Which columns are unique ?
Is this in a Stored proc, inline SQL, or something else ?

Kenny Blankenship
Starting Member

25 Posts

Posted - 2004-06-11 : 09:18:35

Thanks ditch for the reply. That did the trick.

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-06-11 : 09:18:57

Ditch, you can't use ORDER BY in derived tables, sorry.

EDIT!
Ignore me ! Just went away and had a play after posting. You can ORDER BY if you use TOP. Handy that ! Sorry Ditch!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources