find repeated values

Hi,

I've a table like below
:sno: :name:
123 abc
234 xyz
123 orp
984 wer
912 nbvm
912 iirrrr

i want to find out how many sno are repeated and its values using query.
please help

DNK

Re: find repeated values

You didn't specify what database you're using:
SQL Server (untested)
select T.sno, T.name from table T where exists (select * from table group by
sno having count(sno) > 1 and sno = T.sno)

"DNKMCA" wrote in message
news:O5ZlT5a%23FHA.1032@TK2MSFTNGP11.phx.gbl...
> Hi,
>
> I've a table like below
> :sno: :name:
> 123 abc
> 234 xyz
> 123 orp
> 984 wer
> 912 nbvm
> 912 iirrrr
>
> i want to find out how many sno are repeated and its values using query.
> please help
>
> DNK
>
>

Re: find repeated values

DNKMCA wrote:
> Hi,
>
> I've a table like below
>> sno: :name:
> 123 abc
> 234 xyz
> 123 orp
> 984 wer
> 912 nbvm
> 912 iirrrr
>
> i want to find out how many sno are repeated and its values using
> query. please help
>
What database are you using? Type and version, please.

How do you want to see the results? do you just want a single number? IE, 2
duplications in this set of sample data? This query will give you that (this
may not work in all databases - it will work in SQL Server):

select count(*) duplicates from
(select sno, count(*) from yourtable
group by sno having count(*) > 1) q

Or do you want to actually see the duplicate sno's?
123 2
912 2

This query will do that:
select sno, count(*) from yourtable
group by sno having count(*) > 1

Or do you want to see the values in name?
123 abc
123 orp
912 nbvm
912 iirrrr

Try this:

select m.sno, name from yourtable m inner join
(select sno, count(*) from yourtable
group by sno having count(*) > 1) q
on m.sno = q.sno


Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.