greeting,

i have 1 question that need hints to proceed for the rest of my work.
i have a table: VENDOR. its column c/w vendorid, name...

PROBLEM: i need a query that can find the all duplicate name exist in the table since the vendorid is PK.

example:
vendorid name
001 ABC.com
002 ABC.com

my idea is used this condition :

if count(name) > 1 /* which NAME appear more than 1 times, duplicate */
then return the vendorid

anyone pls guide me how on it? just simply query that can extract the data from the table..

thank you

Hi,
and which vendorid will be returned if you have e.g. three same names with different vendorid. The first, second, or third?
Peko

You can do something like this:

SELECT name
        FROM VENDOR
        GROUP BY name
        HAVING count(name) > 1

This will return all duplicate names.

1st and foremost, thanks for answering

ok, using group by and having clause did really can sort accordingly. but somehow drawback is it doesn't shown the related vendorid c/w the name.

seems easy in logic but somehow hard to apply over query

condition is i dunno how to apply search condition over WHERE clause

e.g: WHERE vendorid = ???
     group by name
     having count(name)>1

how can i find out any duplicate name, with diffrent vendorid. sequence is not important, as long as it show all the tuples that meet this requirement

any query that can return the vendorid that come along with its related duplicate name?

thank you

A CTE (Common Table Expression) is what you need. Such as:

WITH Test AS (SELECT name
        FROM Vendor
        GROUP BY name
        HAVING count(name) > 1)
SELECT Vendor.VendorID,
                Test_1.name
FROM Vendor
INNER JOIN Test AS Test_1 ON Test_1.name = dbo.Vendor.name

Hope this helps...

My apologies if you are not using SQL Server 2005. I posted the previous answer without even thinking about it. I will put together a SQL 2000 answer and repost.

Again, my apologies.

Scott

OK, here you go. For SQL Server 2000...

select VendorID, name from Vendor WHERE name in (SELECT name
        FROM Vendor
        GROUP BY name
        HAVING count(name ) > 1)

Scott

thanks for all precious ideas

it do really work on what i want now...

thank you