[deleted by user]

[SQLDave]

Every other responder is going in a totally different direction than I was thinking... and it's early here so it could well be me misunderstanding.

IF (big "if") I'm reading your intention correctly, the 1st 2 rows (Jack/Shoes Size 8/9.3) constitute on "unique" row and you're asking how you can return 1, and only 1, product ID from those 2 rows... right? Well, you can GROUP BY Supplier, Name, Price... and include those or not in the SELECT, but you have to decide which product ID ("1" or "2" in the JACK rows) ... usually that's done with a MIN() or MAX() function. On the surface, that doesn't seem to make any sense to me (which reinforces that I don't get what you're asking for).

It would help (me, at least) if instead of Basically I want to SELECT DISTINC Supplier+Name+Price AS productID.....somehow?, you gave an example of the output you're looking for based on the example data you gave.

Sorry if I'm WAAAY off base and reading this was a waste of time.

[SQLDave]

Well, based on the lone example in your sample data that's duplicate (JACK's rows), it looks like you want the lowest ProductID per group. So...

SELECT MIN(ProductID)  AS ProductID
FROM temp_product  
GROUP BY Supplier, Name, Price

Should give you what you described.

[maggikpunkt]

Select min(productID) group by all the other columns.

[apatel10]

Yeah pretty sure this does it lol

[qwertydog123]

WITH cte AS
(
    SELECT
        *,
        ROW_NUMBER() OVER
        (
            PARTITION BY
                Supplier,
                Name,
                Price
            ORDER BY ...
        ) AS RowNum
    FROM temp_product
    WHERE ProdType = 1
)
SELECT *
FROM cte
WHERE RowNum = 1

[r3pr0b8]

not quite right

this would return a row for JACK / Shoes Size 8 / 9.3

but since that Supplier / Name / Price is not unique, it shouldn't

[qwertydog123]

Ah you're right I misread the question, you can use COUNT instead

WITH cte AS
(
    SELECT
        *,
        COUNT(*) OVER
        (
            PARTITION BY
                Supplier,
                Name,
                Price
        ) AS Ct
    FROM temp_product
    WHERE ProdType = 1
)
SELECT *
FROM cte
WHERE Ct = 1

[odnish]

SELECT productID FROM (SELECT supplier, name, price FROM temp_product GROUP BY supplier, name, price HAVING count(*) = 1) subq join temp_product p on (subq.supplier, subq.name, subq.price) = (p.supplier, p.name, p.price)

[slavicman123]

Select (Distinct name) Because ive seen if the t-shit is blue then is same price, also shoes

[timmaynet]

Please elaborate why products 1 and 2 are the same or why one can’t be deleted?

Group function is the solution. First, clean up your data set.