Selecting records that exist more than once

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

Selecting records that exist more than once

Author

Topic

tallsimon67
Starting Member

3 Posts

Posted - 2009-07-29 : 05:55:11

Hi,

Can anyone help with this issue.

I have a table with data in that i want to find out where duplicates exist after performing a select distinct.

The data:

JOB_NO APPOINTMENT
56B0001 T
56B0001 T
56B0001 T
56S0001 P
56S0001 P
56S0001 M

Select Distinct JOB_NO, APPOINTMENT

56B0001 T
56S0001 P
56S0001 M

If i nest this query in

Select
JOB_NO, count(*) as JOBNOCount
from (Select Distinct JOB_NO, APPOINTMENT)
Group By
JOB_NO

I get msg 156 Incorrect syntax near the keyword 'group'

The idea was to show records where JOB_NO > 1

any help on this would be great

Many thanks

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-07-29 : 06:10:55

quote:
[i]
The idea was to show records where JOB_NO > 1

any help on this would be great

Many thanks

Not able to understand this part.. what do mean by JOB_NO > 1?


DECLARE @T TABLE
(JOB_NO VARCHAR(10),APPOINTMENT VARCHAR(1))

INSERT INTO @T SELECT
'56B0001', 'T' UNION ALL SELECT
'56B0001', 'T' UNION ALL SELECT
'56B0001', 'T' UNION ALL SELECT
'56S0001', 'P' UNION ALL SELECT
'56S0001', 'P' UNION ALL SELECT
'56S0001', 'M'

SELECT job_no, COUNT(*)
FROM @t
GROUP BY Job_no
HAVING  COUNT(*) >1

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

tallsimon67
Starting Member

3 Posts

Posted - 2009-07-29 : 06:20:18

The records 56B0001 are all the same so i wouldn't want to see them as they all have a T as appointment. This is why I was looking to use SELECT DISTINCT as that would return one record.

The 56S0001 records have both P and M and so I want to see 56S0001 in the output.

sorry if i wasn't clear on my early post.

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-07-29 : 06:25:14

Again I think I'm confuse with your requirement

see this -


DECLARE @T TABLE
(JOB_NO VARCHAR(10),APPOINTMENT VARCHAR(1))

INSERT INTO @T SELECT
'56B0001', 'T' UNION ALL SELECT
'56B0001', 'T' UNION ALL SELECT
'56B0001', 'T' UNION ALL SELECT
'56S0001', 'P' UNION ALL SELECT
'56S0001', 'P' UNION ALL SELECT
'56S0001', 'M'

SELECT job_no, COUNT(DISTINCT APPOINTMENT)
FROM @t
GROUP BY Job_no
HAVING  COUNT(DISTINCT APPOINTMENT) >1

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-29 : 06:26:49

[code]
SELECT JOB_NO, COUNT(*) AS JOBNOCount
FROM
(
SELECT DISTINCT JOB_NO, APPOINTMENT
FROM atable
) t
GROUP BY JOB_NO
[/code]

KH
[spoiler]Time is always against us[/spoiler]

tallsimon67
Starting Member

3 Posts

Posted - 2009-07-29 : 06:39:40

Thank you Mangal that is perfect

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources