display duplicates

[Jaison Jose]

Hi Champs,

I do have a table in which machine info is stored. It does has lot of
duplicate entries. I have written a simple code to list them..

select a.Name0,count(*) from v_r_system group by Name0 having count(*)>1

This code list me all the duplicate machine entries(258 rows) with the count.

Now, i would like to join 2 more tables and give some additional condition
to fetch the data.

I did try wrting like below but in vain! It dose not result me what i
want(only two rows listed).

select a.Name0,count(*),a.Obsolete0,a.Client0,b.LastScandate as
LastSWscan,c.LastHWscan from v_r_system a
left outer join v_GS_LastSoftwareScan b on a.resourceid=b.resourceid
left outer join v_GS_WORKSTATION_STATUS c on a.resourceid=c.resourceid
group by a.Name0,a.Obsolete0,a.Client0,b.LastScandate,c.LastHWscan
having count(*) > 1

Could anyone point out what have to be corrected in the above code?

Thanks a ton for your help in advance.

Cheers,
JJ

[Plamen Ratchev]

Try these queries:

SELECT DISTINCT Name0, cnt, Obsolete0, CLient0, LastSWscan, LastHWscan
FROM (
SELECT a.Name0,
COUNT(*) OVER(PARTITION BY a.Name0) AS cnt,
a.Obsolete0,
a.Client0,
b.LastScandate AS LastSWscan,
c.LastHWscan
FROM v_r_system AS a
LEFT OUTER JOIN v_GS_LastSoftwareScan AS b
ON a.resourceid = b.resourceid
LEFT OUTER JOIN v_GS_WORKSTATION_STATUS AS c
ON a.resourceid = c.resourceid) AS T
WHERE cnt > 1;

or

SELECT DISTINCT
a.Name0,
d.cnt,
a.Obsolete0,
a.Client0,
b.LastScandate AS LastSWscan,
c.LastHWscan
FROM v_r_system AS a
INNER JOIN (SELECT Name0, COUNT(*) AS cnt
FROM v_r_system
GROUP BY Name0
HAVING COUNT(*) > 1) AS d
ON a.Name0 = d.Name0
LEFT OUTER JOIN v_GS_LastSoftwareScan AS b
ON a.resourceid = b.resourceid
LEFT OUTER JOIN v_GS_WORKSTATION_STATUS AS c
ON a.resourceid = c.resourceid;

or

SELECT DISTINCT
a.Name0,
a.Obsolete0,
a.Client0,
b.LastScandate AS LastSWscan,
c.LastHWscan
FROM v_r_system AS a
LEFT OUTER JOIN v_GS_LastSoftwareScan AS b
ON a.resourceid = b.resourceid
LEFT OUTER JOIN v_GS_WORKSTATION_STATUS AS c
ON a.resourceid = c.resourceid
WHERE a.Name0 IN (SELECT Name0
FROM v_r_system
GROUP BY Name0
HAVING COUNT(*) > 1);

--
Plamen Ratchev
http://www.SQLStudio.com

[Jaison Jose]

Hey Dude,

YOU ARE THE MAN!!

Excellent code!..All three works fine.

Thanks a ton :-)

Cheers,
JJ

"Plamen Ratchev" wrote:

> .
>