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

1

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

Example
Example1087×161 8.96 KB

2 

;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;
3

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?

4

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;
1 Like
5

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!

1 Like