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
 ROW_NUMBER function

Author  Topic 

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2013-06-20 : 11:26:17
Hi All - I have the following table:
[CODE]
TEL NAME
4165551234 JAMES
4165551234 ADAM
4165559999 JENN
4165559999 STEPH
[/CODE]

I'm using the ROW_NUMBER function to generate a "row_number" for each Telephone number:
[CODE]

SELECT
TEL,
NAME,
ROW_NUMBER() OVER (PARTITION BY TELL ORDER BY NEWID()) 'ROWNUM'

FROM TABLE T1

TEL NAME ROWNUM
4165551234 JAMES 1
4165551234 ADAM 2
4165559999 JENN 1
4165559999 STEPH 2
[/CODE]

However I need it to output only records that have a ROWNUM value=1.
The code below doesn't allow windowed functions for this. How can this be corrected? THANKS!

[CODE]

SELECT
TEL,
NAME,
ROW_NUMBER() OVER (PARTITION BY TEL ORDER BY NEWID()) 'ROWNUM'

FROM TABLE T1

WHERE ROW_NUMBER() OVER (PARTITION BY TEL ORDER BY NEWID())=1
[/CODE]

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-20 : 11:36:23
You cannot use row_number function in a where clause, so wrap your query in a cte or subquery and then use the where clause in the outer query:
SELECT TEL, NAME FROM (
SELECT
TEL,
NAME,
ROW_NUMBER() OVER (PARTITION BY TEL ORDER BY NEWID()) 'ROWNUM'

FROM TABLE T1
)s WHERE ROWNUM = 1
Go to Top of Page

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2013-06-20 : 11:45:55
Terrific! Works great!
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-06-21 : 08:13:36
More on row_number() http://beyondrelational.com/modules/2/blogs/70/posts/10802/multipurpose-rownumber-function.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -