Limiting records returned by a UNION

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)

Limiting records returned by a UNION

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-09-13 : 09:54:18

Ziggy writes "I have a query that returns data from three tables - using UNION ALL. My aim is to retrieve and display a value that reflects how popular particular articles are (hence UNION ALL, as some records have the same popularity value, and I want to return all these records).

Using UNION ALL, I can successfully combine my three tables, and output a list, which uses a single ORDER BY statement to list out records ranked on their popularity.

However, I want to limit the results so that I get only 6-8 records returned in total. Using something like SELECT TOP 6 within each of the three individual queries does not provide the desired results. Is there some way to get around this?

Running SQL server 2000, OS is Windows 2000"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-09-13 : 10:32:58


select top 6 a.*
from (
select ....
union all
select ....
union all
select .... ) a

Jay White
{0}

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-09-13 : 19:49:26

I'm not sure if this is going to be any better but here goes


INSERT INTO #Articles
SELECT top 3 * from TableA

INSERT INTO #Articles
SELECT top 3 * from TableB

INSERT INTO #Articles
SELECT top 3 * from TableC

SELECT * FROM #Articles

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-09-14 : 05:40:56

Page47's code should do it, but make sure you use an ORDER BY every time you use TOP in your SELECT.

But if you don't care which rows get to be in TOP x, then you can ommit ORDER BY.

--
HTH,
Vyas
http://vyaskn.tripod.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources