Order By inside 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

General SQL Server Forums

New to SQL Server Programming

Order By inside Union

Author

Topic

Tahumars
Starting Member

15 Posts

Posted - 2009-10-09 : 13:04:24

I know that you cannot use an Order by inside queries when doing a union, however, in my case I'm not sure how else to do it.

My query is something along the lines of:

(select top 5 category, item, cost from table1 where category = 'x' order by cost desc) union all
(select top 5 category, item, cost from table2 where category = 'y' order by cost desc)

Now in my case, taking the order by outside and putting it in the end will not work. I won't be getting the top 5 costliest items, instead I'll be getting the 5 items from each category that were inserted first and then sorted by cost.

So I guess what I'm looking for is how to get a union of several ordered queries.

I'm also interested if there is any other way to approach the greater problem. I need to get the top 5 items (by cost) per category for about 26 categories (my sample above is obviously shortened)

What I need to see is

Category Item Cost
A ABC 50
A DEF 43
A GHI 32
A JKL 31
A MNO 14
B DEF 44
B GHI 40
B JKL 35
B ABC 15
B MNO 6
. . .
. . .
. . .

I could find no other way of doing it other than writing a cursor to loop through the categories and writing unioned queries. And you can see above what my problem is with that idea.

Thanks for any advice you can give,

Tahumars

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-09 : 13:10:15

are you using sql 2005? if yes, use below


SELECT *
FROM
(
SELECT Category, Item, Cost
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Cost DESC) AS Seq,Category, Item, Cost
From table1)t
WHERE Seq<=5
)p
UNION ALL
SELECT *
FROM
(
SELECT Category, Item, Cost
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Cost DESC) AS Seq,Category, Item, Cost
From table2)t
WHERE Seq<=5
)q

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-09 : 13:16:11

or even


SELECT *
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Cost DESC) AS Seq,Category, Item, Cost
From (SELECT Category, Item, Cost FROM table1
      UNION ALL
      SELECT Category, Item, Cost FROM table2)t
)p
WHERE Seq<=5

Tahumars
Starting Member

15 Posts

Posted - 2009-10-09 : 14:43:03

Thanks a lot for your help visakh. With some minor changes, both of your queries worked. In case you are interested:

The first query you posted returned correct values, except for some reason it was returning them twice. There were 260 rows returned where there should have been only 130. I tried adding a distinct to the very top SELECT *, but that didn't work. I ended up putting a SELECT DISTINCT * (yourQuery) and that worked.

The second query also worked to a degree, but would show

CatA item1
CatA item2
CatA item2
CatA item3
CatA item3
CatB item1
CatB item2
CatB item2
CatB item3
CatB item3

So it would show 5 rows, but would repeat the second and third. I played around with it a bit and got it to work as:

SELECT distinct category, item, cost
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY category ORDER BY cost DESC) AS Seq, category, item, cost
From (SELECT category, item, cost FROM table1
      UNION ALL
      SELECT category, item, cost FROM table2)t
)p
WHERE Seq<=9
order by category desc, cost desc

Thanks again for your help.

(Also, yes I am using SQL2005.)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-09 : 14:45:12

i assumed you didnt have same items on both the tables. was that not the case?

Tahumars
Starting Member

15 Posts

Posted - 2009-10-09 : 15:28:05

I just realized my mistake. In the first post I called them table1 and table2 while actually they should both just be table1, as there is only one table. Mea Culpa.

Tahumars
Starting Member

15 Posts

Posted - 2009-10-09 : 15:37:39

Given that mistake, does having only one table simplify those queries at all? Would it become:

SELECT distinct category, item, cost
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY category ORDER BY cost DESC) AS Seq, category, item, cost
From (SELECT category, item, cost FROM table1)t
)p
WHERE Seq<=5
order by category desc, cost desc

Now that I've seen them here I'm looking up partitions to see exactly what they are, so sorry if I'm a little slow on the uptake.

EDIT: And it does indeed work. Thanks again.
EDIT2: For anyone else that has this problem and wants some more info on RANK and PARTITION, this is a great starter guide: http://weblogs.sqlteam.com/jeffs/archive/2007/03/28/60146.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-11 : 14:28:47

quote:
Originally posted by Tahumars

Given that mistake, does having only one table simplify those queries at all? Would it become:

SELECT distinct category, item, cost
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY category ORDER BY cost DESC) AS Seq, category, item, cost
From (SELECT category, item, cost FROM table1)t
)p
WHERE Seq<=5
order by category desc, cost desc

Now that I've seen them here I'm looking up partitions to see exactly what they are, so sorry if I'm a little slow on the uptake.

EDIT: And it does indeed work. Thanks again.
EDIT2: For anyone else that has this problem and wants some more info on RANK and PARTITION, this is a great starter guide: http://weblogs.sqlteam.com/jeffs/archive/2007/03/28/60146.aspx

actually you can simplify above as


SELECT distinct category, item, cost
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY category ORDER BY cost DESC) AS Seq, category, item, cost
From table1
)p
WHERE Seq<=5
order by category desc, cost desc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources