Tidying Row_Number sub-query - 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)

Tidying Row_Number sub-query

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2011-10-12 : 06:44:24

Hi,

I have a query that gets ordered by a new column created as part of a sub-query. It's now become necessary that I add a Row_Number() to the query so that I can do a LIMIT style clause (i.e. get records between 5000 and 10000). But the result is a horrible mess, and involves repeating the sub-query twice:


select ROW_NUMBER() over (order by
    (select count(intItem1RowId) as tr from [Table1] r
         where r.intItem2RowId = m.intItem2RowId
         and r.strRelName = 'dt-na') , intWeight desc ) 
    as rn, m.[intItem1RowId], m.[intItem2RowId],
(select count(intItem1RowId) as tracks from [Table1] r
 where r.intItem2RowId = m.intItem2RowId
 and r.strRelName = 'dt-na') as tr
FROM [Table1] m
WHERE m.[strRelName]  = 'da-na' order by tracks, intWeight desc

Is there a neater way to render this code?

Also, when I add this clause at the bottom:

and rn > 500

I'm told that 'rn' is an invalid column name. What gives there?

Cheers,
Matt

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-12 : 07:01:08

maybe

with cte as
(
select m.[intItem1RowId], m.[intItem2RowId],
(select count(intItem1RowId) as tracks from [Table1] r
where r.intItem2RowId = m.intItem2RowId
and r.strRelName = 'dt-na') as tr
FROM [Table1] m
WHERE m.[strRelName] = 'da-na'
)
select ROW_NUMBER() over (order by tr) as rn, *
from cte
order by tracks, intWeight desc

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mattt
Posting Yak Master

194 Posts

Posted - 2011-10-12 : 07:05:12

Much neater, thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources