Duplicates

Hi all,
This works fine to show duplicates of NumbB Column. I want to show the
duplicates and = different lastnames with those that
are duplicates.
Select id,NumbA,NumbB,Lastname,firstname,typepr
oc,dateofex from PC
where NumbB in(select NumbB from PC
group by NumbB
having count(NumbB)>1)order by NumbB
thanks
GVOne general approach is:
SELECT * -- use column names
FROM tbl t1
WHERE ( SELECT COUNT( * )
FROM tbl t2
WHERE t2.col <= t1.col ) >= 2 ;
As a side note, for such questions, always post table DDLs & sample data
along with expected results so that others can understand your requirements
better.
Anith|||> This works fine to show duplicates of NumbB Column. I want to show the
> duplicates and = different lastnames with those that
> are duplicates.
Can you rephrase what you want?
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"gv" wrote:

> Hi all,
> This works fine to show duplicates of NumbB Column. I want to show the
> duplicates and = different lastnames with those that
> are duplicates.
> Select id,NumbA,NumbB,Lastname,firstname,typepr
oc,dateofex from PC
> where NumbB in(select NumbB from PC
> group by NumbB
> having count(NumbB)>1)order by NumbB
> thanks
> GV
>
>|||Hi again and thanks
Ok, thanks for your help
I want to pull duplicates of Col2, order them by Col2 and were
Col3(lastname) is the same within the duplicates.
SELECT Col1,Col2,Col3,Col4,Col5
FROM Table1
WHERE Col2 IN(SELECT Col2 FROM Table1
GROUP BY Col2
HAVING COUNT(Col2)>1)
ORDER BY Col2
Sample rows
Col1 Col2 Col3 Col4 Col5
532 000 doe J Intraductal EUS
8675 000 loe S Gastric
266 240 Smith D Pancreatic
266 240 Smith D Pancreatic
193 029 VuV N Esophagus
193 029 VuV N Esophagus
836 632 QQQ F Gastric
So would like the return to be:
Col1 Col2 Col3 Col4 Col5
266 240 Smith D Pancreatic
266 240 Smith D Pancreatic
193 029 VuV N Esophagus
193 029 VuV N Esophagus
thanks
GV
"gv" <viatorg@.musc.edu> wrote in message
news:eCNoS2kAFHA.3236@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> This works fine to show duplicates of NumbB Column. I want to show the
> duplicates and = different lastnames with those that
> are duplicates.
> Select id,NumbA,NumbB,Lastname,firstname,typepr
oc,dateofex from PC
> where NumbB in(select NumbB from PC
> group by NumbB
> having count(NumbB)>1)order by NumbB
> thanks
> GV
>|||Try (not tested),
SELECT a.Col1,a.Col2,a.Col3,a.Col4,a.Col5
FROM
Table1 as a
inner join
(
SELECT Col2, col5
FROM Table1
GROUP BY Col2, col5
HAVING COUNT(*) > 1
) as b
on a.col2 = b.col2 and a.col5 = b.col5
order by
a.col2, a.col5
AMB
"gv" wrote:

> Hi again and thanks
>
> Ok, thanks for your help
> I want to pull duplicates of Col2, order them by Col2 and were
> Col3(lastname) is the same within the duplicates.
>
> SELECT Col1,Col2,Col3,Col4,Col5
> FROM Table1
> WHERE Col2 IN(SELECT Col2 FROM Table1
> GROUP BY Col2
> HAVING COUNT(Col2)>1)
> ORDER BY Col2
> Sample rows
> Col1 Col2 Col3 Col4 Col5
> 532 000 doe J Intraductal EUS
> 8675 000 loe S Gastric
> 266 240 Smith D Pancreatic
> 266 240 Smith D Pancreatic
> 193 029 VuV N Esophagus
> 193 029 VuV N Esophagus
> 836 632 QQQ F Gastric
> So would like the return to be:
> Col1 Col2 Col3 Col4 Col5
> 266 240 Smith D Pancreatic
> 266 240 Smith D Pancreatic
> 193 029 VuV N Esophagus
> 193 029 VuV N Esophagus
>
> thanks
> GV
>
>
>
>
>
> "gv" <viatorg@.musc.edu> wrote in message
> news:eCNoS2kAFHA.3236@.TK2MSFTNGP15.phx.gbl...
>
>