using row_number in the same query

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)

using row_number in the same query

Author

Topic

zion99
Posting Yak Master

141 Posts

Posted - 2009-03-10 : 06:10:36

Hi,
I have a query like:


select 
row_number() over (partition by col1, col2, col3
	order by col1, col2, col3) as rowid
,col1, col2, col3, col4, col5
from table1

i require only the top rows of each combination of col1, col2 & col3 and hence use rowid = 1, but for that i can't use the foll.


select 
row_number() over (partition by col1, col2, col3
	order by col1, col2, col3) as rowid
,col1, col2, col3, col4, col5
from table1
where rowid = 1

workaround is to use a subquery. Can anybody please suggest me a better way of doing this?

pootle_flump

1064 Posts

Posted - 2009-03-10 : 06:12:00

You need to refer to the result set - analytic functions are only available in the SELECT clause, not WHERE.

select *
from --me numbers
(select 
row_number() over (partition by col1, col2, col3
	order by col1, col2, col3) as rowid
,col1, col2, col3, col4, col5
from table1) as me_numbers
where rowid = 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-10 : 06:56:10

The windowed function are also available in the ORDER BY clause.

E 12°55'05.63"
N 56°04'39.26"

zion99
Posting Yak Master

141 Posts

Posted - 2009-03-10 : 09:55:09

hi Peso, sorry but i didnt quite understand!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-10 : 10:00:50

Pootle wrote that "analytic functions are only available in the SELECT clause, not WHERE." and I posted a supplemental piece of information.
Windowed function (correct name) are also available in the ORDER BY clause, not only the SELECT clause.

E 12°55'05.63"
N 56°04'39.26"

pootle_flump

1064 Posts

Posted - 2009-03-10 : 11:13:11

I'm not the only one that uses the term!
http://www.google.co.uk/search?hl=en&q=%22analytic+functions%22+%22sql+server%22&btnG=Search&meta=

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-10 : 11:25:24

I'll stick with "Window function" since Books Online has that name for them
http://msdn.microsoft.com/en-us/library/ms189461.aspx

E 12°55'05.63"
N 56°04'39.26"

pootle_flump

1064 Posts

Posted - 2009-03-10 : 12:31:03

Interesting:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/analysis.htm
It's Oracle, but that has windows functions as a subset of analytic functions. Coo.

The gist is, when I first came across these they were introduced to me as analytic functions and it has stuck....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources