Tricky SQL Problem, Counting Dupes
Say I have a table like
col1 col2
6 4
3 7
3 3
3 3
2 5
1 4
2 2
2 2
2 2
8 6
8 3
I want to write a sql statement that will count the dupes so it would look like:
col1 col2
dups
6 4
1
3 7
1
3 3
1
3 3
2
2 5
1
1 4
1
2 2
1
2 2
2
2 2
3
8 6
1
8 3
1
Is this possible without a cursor? Thanks
October 7th, 2011 7:23pm
Try the
ROW_NUMBER() function. In your case...
SELECT col1, col2, ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col1, col2)
FROM xyz
Free Windows Admin Tool Kit Click here and download it now
October 7th, 2011 8:58pm
Just note that Randy's solution will work on SQL Server 2005 and higher.
let us know if you want to do it in lower version of sql server or other databaseshttp://www.rad.pasfu.com
October 7th, 2011 9:55pm
Wow, that worked, thanks!
Free Windows Admin Tool Kit Click here and download it now
October 11th, 2011 11:53am
This topic is archived. No further replies will be accepted.
Other recent topics
