Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
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.
Hi All - I have the following table:[CODE]TEL NAME4165551234 JAMES4165551234 ADAM4165559999 JENN4165559999 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 T1TEL NAME ROWNUM4165551234 JAMES 14165551234 ADAM 24165559999 JENN 14165559999 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 T1WHERE 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