Distinct UNION with Row Number

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

Distinct UNION with Row Number

Author

Topic

BigMeat
Yak Posting Veteran

56 Posts

Posted - 2009-02-12 : 23:58:06

Hi

I was wondering is it possible to apply teh RowNumber feature in a UNION statement. Also is it possible to a distinct on teh overall UNION statement. Below is an example of what I am trying to do:

SELECT CustomerName WHERE <Complex JOIN and WHERE statement 1>
UNION
SELECT CustomerName WHERE <Complex JOIN and WHERE statement 2>

It is possibel that CustomerName coudl be returned twice as it coudl be found in Statement 1 and Statement 2. Is it possible to do an over all distinct to ensure that Customername is only returned once?

Also is it possible to add a row numbe feature on teh overall statement so for example I can say only return me rows between 20 and 30 of teh 200 results for example.

The only method I can think of is to use a Temporary Table, but im not sure if this is the best mehod? im using SQL Server 2008 so would welcome the use of any new cool features thar might be available.

Many thanks in advance

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-13 : 00:15:17

Union will return only distinct customers and u can use table variables instead of Temporary table....

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-13 : 00:17:22

try this

Declare @temp table ( id int identity(1,1),customername varchar(256))
insert into @temp
SELECT CustomerName WHERE <Complex JOIN and WHERE statement 1>
UNION
SELECT CustomerName WHERE <Complex JOIN and WHERE statement 2>

select customername from @temp where id between 20 and 30

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 01:01:45

[code]
SELECT CustomerName
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY CustomerName) AS Seq,CustomerName
FROM
(
SELECT CustomerName WHERE <Complex JOIN and WHERE statement 1>
UNION
SELECT CustomerName WHERE <Complex JOIN and WHERE statement 2>
)t
)r
WHERE Seq BETWEEN 10 AND 20
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources