Merge 2 queries - 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 2008 Forums

Transact-SQL (2008)

Merge 2 queries

Author

Topic

s2002
Starting Member

49 Posts

Posted - 2010-08-31 : 06:48:14

Hello
I have table as Follow
ProductID Name CategoryID ReleasedDate
1 football 1 1-1-2000
2 Chess 1 5-1-2000
3 Cheese 2 6-1-2000
...
Table has 1000+ records. I want to query for top 3 products where CategoryID= 1 and also top 3 products where CategoryID= 2

I could use below queries

select top (3) from products where category ID = 1 orderby RelasedDate
select top (3) from products where category ID = 2 orderby RelasedDate

But I want to merge above queries into one query.So the result should be 6 records(3 records for CategoryID = 1 and 3 for Cat 2).
Is it possible?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-31 : 06:52:27

select * from
(
select ...
UNION
select ...
) as dt

No, you're never too old to Yak'n'Roll if you're too young to die.

s2002
Starting Member

49 Posts

Posted - 2010-08-31 : 07:48:06

webfred
Thanks for Reply.
I tried Union Command before. while check Sql Profiler , I didn't notice execution time/resource usage difference using 2 queries or Union command.
I want to show result in 2 GridViews(ASP.Net web pages). My purpose is to optimize performance.
So could you please answer if Union affect performance in above scenario?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-08-31 : 08:38:51

If the result of the 2 queries are mutually exclusive, You can replace UNION with UNION ALL.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-31 : 08:41:07

I don't think it will affect the performance noticeable.
But if the two selects always bringing up different results then you can use
UNION ALL
because then there is no need for SQL to have a look for duplicate records.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-31 : 08:44:41

Also check this solution:

select * from
(
select
row_number() over (partition by categoryID order by ReleaseDate) as rnum,
*
from products where categoryID in (1,2)
) as dt
where rnum < 4

No, you're never too old to Yak'n'Roll if you're too young to die.

s2002
Starting Member

49 Posts

Posted - 2010-08-31 : 08:47:07

quote:
Originally posted by webfred

I don't think it will affect the performance noticeable.

Does any one know if SQL treats Union command as batch process or seprate processes.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources