SQL: ROW_NUMBER() OVER (PARTITION BY ... ORDER BY

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

SQL: ROW_NUMBER() OVER (PARTITION BY ... ORDER BY

Author

Topic

Amber_Deslaurier
Starting Member

40 Posts

Posted - 2010-09-16 : 21:24:56

Hi,

I have 3 fields:

ACCOUNT NUMBER (UNIQUE PRIMARY KEY)
CLIENT LAST NAME
CLIENT FIRST NAME

I would like to extract specifically by Last name that matches any last names in the table provided that the first name matches the first name in the table... I would like it to be ranked ie provide a row number so I know how many duplicates exist... but i would only like to see the duplicates and not unique last names.

I still need the ACCOUNT NUMBER in my select since I need to verify details...

what the best way to do this?

Thanks,

Amber.

Amber_Deslaurier
Starting Member

40 Posts

Posted - 2010-09-16 : 21:27:25

EXAMPLES:

1. 79856 MARY SMITH
2. 85423 MARY SMITH

1. 85426 CHRIS MAU
2. 22123 CHRIS MAU
3. 00235 CHRIS MAU

etc...

PavanKK
Starting Member

32 Posts

Posted - 2010-09-17 : 03:19:42

SELECT ROW_NUMBER()OVER(PARTITION BY [CLIENT FIRST NAME],[CLIENT LAST NAME] ORDER BY [ACCOUNT NUMBER]) RNo,[ACCOUNT NUMBER],[CLIENT FIRST NAME],[CLIENT LAST NAME]
FROM Tablename

KK

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-09-17 : 08:14:08

select [CLIENT FIRST NAME],[CLIENT LAST NAME],count(*) as Instances
from yourTable
group by [CLIENT FIRST NAME],CLIENT LAST NAME]
having count(*) > 1

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources