Using Union with order by sub-clauses

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 2008 Forums

Transact-SQL (2008)

Using Union with order by sub-clauses

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2011-07-01 : 04:38:40

I need to write a query which picked the top two "itemTypes" from a variety of types, ordered by their "weight" for a particular "userId" and then join them all together into a single result set.

My first attempt - which will hopefully illustrate what I'm trying to do - looked like this:


select top 2 * from (select top 1000 * from [myTable]
where userId = @userId and itemType = 1 order by weight)

union select top 2 * from (select top 1000 * from [myTable]
where userId = @userId and itemType = 2 order by weight)

union select top 2 * from (select top 1000 * from [myTable]
where userId = @userId and itemType = 3 order by weight)

But this won't run because it seems you can't put an order by into a fragment of a union query.

Seems like quite a straightforward scenario but in my stupidity, I can't figure out the correct syntax to get the job done - if someone could point it out it'd be much appreciated.

And while we're about it, out of curiosity, I'm sure there must be a better way to frame the subquery than putting "top 1000" at the front in order to avoid the error "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."

Cheers,
Matt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-01 : 04:53:49

[code];WITH cteSource
AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ItemType ORDER BY [Weight]) AS SeqID
FROM dbo.MyTable
WHERE UserID = @UserID
)
SELECT *
FROM cteSource
WHERE SeqID <= 2[/code]

N 56°04'39.26"
E 12°55'05.63"

mattt
Posting Yak Master

194 Posts

Posted - 2011-07-01 : 05:28:50

Great, thanks.

I've seen partition in use before but not sure what it does - must read up.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources