ROW_NUMBER with UNION ALL SELECT statements

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)

ROW_NUMBER with UNION ALL SELECT statements

Author

Topic

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2012-02-07 : 09:52:45

I have a query similar to this:


SELECT Col1, ROW_NUMBER() OVER (ORDER BY Col1)
FROM SomeTbl
UNION ALL
SELECT Col1, ROW_NUMBER() OVER (ORDER BY Col1)
FROM SomeTbl
WHERE Col2 > 100

I want to inject ROW_NUMBER() but ROW_NUMBER() restarts at 1 on the second SELECT statement.

I could make it a subquery by wrapping it inside the FROM clause like this:


SELECT Col1, ROW_NUMBER() OVER (ORDER BY Col1)
FROM
(
SELECT Col1
FROM SomeTbl
UNION ALL
SELECT Col1
FROM SomeTbl
WHERE Col2 > 100
)subquery

But I was wondering if there's an alternative solution without using derived tables. Is it possible to reference the first SELECT statement and, somehow, continue counting where it left off?

Thank you very much for any help, in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-07 : 12:34:30

whats the problem with derived table approach?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources