SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 ROW_NUMBER function
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

funk.phenomena
Posting Yak Master

Canada
120 Posts

Posted - 06/20/2013 :  11:26:17  Show Profile  Reply with Quote
Hi All - I have the following table:

TEL        NAME
4165551234 JAMES
4165551234 ADAM
4165559999 JENN
4165559999 STEPH


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


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


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!



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

Edited by - funk.phenomena on 06/20/2013 11:30:36

James K
Flowing Fount of Yak Knowledge

3651 Posts

Posted - 06/20/2013 :  11:36:23  Show Profile  Reply with Quote
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

Canada
120 Posts

Posted - 06/20/2013 :  11:45:55  Show Profile  Reply with Quote
Terrific! Works great!
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 06/21/2013 :  08:13:36  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000