SQLTeam.com | Weblogs | Forums

Find duplicate values in one column and return data from different colums/tables

Hey, I'm not that good at writing sql and I've gotten stuck trying to figure out a way to find rows with duplicate values and returning more data than just the duplicates.

I'm sorry, I didn't get the ctrl+K code formatting to work either...

So the picture below is a pretend data set based on what I need to produce. I'm looking for a way to find all rows with duplicates in eic.itmcod and return a few other things from those rows. From the example picture I want the result to only show row 2,3, 5 and 6 where there are duplicates in eic.itmcod. I have tried Group by X Having count(eic.itmcod)>1 but I can't seem to get that to work for some reason. Excuse my shit code, I hope my question is understandable and have a nice day!

SELECT sup.supnam 'supplier',eic.extitm 'Suppliers Code', eic.itmcod 'Internal Code',itm.itmnam 'Name', eic.extunt 'Unit',eic.extrra 'Amount',itm.basunt 'Base Unit', eic.curprc 'Price',eic.agrnum 'Agreement Number', eic.agvunt as 'Valid Until', CASE WHEN eic.extcod=itm.supcod THEN 'Y' ELSE 'N' END 'Main Supplier'
FROM sup, itm, eic
WHERE itm.compny=eic.compny AND itm.itmcod=eic.itmcod AND eic.extcod=sup.supcod
ORDER BY eic.itmcod


;WITH cte AS
(
    SELECT
        sup.supnam 'supplier',
        eic.extitm 'Suppliers Code',
        eic.itmcod 'Internal Code',
        itm.itmnam 'Name',
        eic.extunt 'Unit',
        eic.extrra 'Amount',
        itm.basunt 'Base Unit',
        eic.curprc 'Price',
        eic.agrnum 'Agreement Number',
        eic.agvunt AS 'Valid Until',
        CASE
            WHEN eic.extcod = itm.supcod THEN
                'Y'
            ELSE
                'N'
        END 'Main Supplier',
        DupCount = SUM(1) OVER (PARTITION BY eic.itmcod)
    FROM
        sup,
        itm,
        eic
    WHERE
        itm.compny     = eic.compny
        AND itm.itmcod = eic.itmcod
        AND eic.extcod = sup.supcod
)
SELECT 
    *
FROM
    cte AS c
WHERE
    c.DupCount > 1
ORDER BY
    c.itmcod;

Thanks for the fast answer. Unfortunately I'm stuck with a severely limited "in client" query builder where I can't use all the normal functions... So I'm unable to use "WITH" for instance. So is there any way around that by dumbing the query down?

If you are able to create objects in the database, you can create a view for the query I posted earlier, and then select from that view in your client.

Alternatively, you can try whether, instead of using a cte, if you made it a subquery, it will work. I suspect it may not because it might then complain about the row_number function. You can still "dumb it down" (as you put it) to avoid using the row_number function, but the query will be less efficient, more cumbersome, and less readable.

If you want to try the subquery approach, change the query to:


SELECT 
    *
FROM
    (
        SELECT
            sup.supnam 'supplier',
            eic.extitm 'Suppliers Code',
            eic.itmcod 'Internal Code',
            itm.itmnam 'Name',
            eic.extunt 'Unit',
            eic.extrra 'Amount',
            itm.basunt 'Base Unit',
            eic.curprc 'Price',
            eic.agrnum 'Agreement Number',
            eic.agvunt AS 'Valid Until',
            CASE
                WHEN eic.extcod = itm.supcod THEN
                    'Y'
                ELSE
                    'N'
            END 'Main Supplier',
            DupCount = SUM(1) OVER (PARTITION BY eic.itmcod)
        FROM
            sup,
            itm,
            eic
        WHERE
            itm.compny     = eic.compny
            AND itm.itmcod = eic.itmcod
            AND eic.extcod = sup.supcod
    ) AS c
WHERE
    c.DupCount > 1
ORDER BY
    c.itmcod;

There we go, now it's working. Thank you very much. And now to taking this apart to learn something myself for the future!

Have a nice day JamesK!