Select row-number across 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 2005 Forums

Transact-SQL (2005)

Select row-number across a union

Author

Topic

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2007-06-14 : 10:46:28

Hi All,

Something I can't get my head around or find much information on. (probably a piece o'cake for you lot)!

I have a select with a union i.e

SELECT a, b FROM t1
UNION
SELECT c, FROM t2

How can I select a row_number from the result of this query for paging, if I insert the row_number for each select I obviously get to different sets of row counts, one for each!

Ideas?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-14 : 10:48:46

use a derived table


select a, b, row_number() over(order by a)
from
(
    SELECT a, b FROM t1
    UNION
    SELECT c, FROM t2
) d

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-14 : 10:49:52

[code]
Select Row_Number() Over (ORDER BY a) AS 'Row Number', a, b
(
SELECT a, b FROM t1
UNION
SELECT c, d FROM t2
) t[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-06-14 : 11:15:04

*Kapow*

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2007-06-15 : 07:40:00

Brilliant! However how do I set a where clause?

Select Row_Number() Over (ORDER BY a) AS 'RowID', a, b
(
SELECT a, b FROM t1
UNION
SELECT c, d FROM t2
) t WHERE RowID > 10

Gives me a 'Invalid column name 'RowID'.'

Or am I just being *&*#&%*(# stupid!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-15 : 07:47:35

[code]Select *
From
(
Select Row_Number() Over (ORDER BY a) AS 'Row Number', a, b
(
SELECT a, b FROM t1
UNION
SELECT c, d FROM t2
) t
) x
Where [Row Number] > 10
[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources