T-SQL: Omit/Ignore repetitive data from a specific column

Question

Hi,

For my question lets consider the following sample table data:

ProductID    ProductName    Price   Category

1                Apple                 5.00       Fruits
2                Apple                 5.00       Food
3                Orange               3.00       Fruits
4                Banana                 2.00       Fruits

I need a query which will result in the following data set:

ProductID    ProductName    Price   Category

1                Apple                 5.00       Fruits
3                Orange               3.00       Fruits
4                Banana                 2.00       Fruits

As you can see ProductID 2 has been omitted/ignored because Apple is already present in the result i.e. each product must appear only once irrespective of Category or Price.

Thanks

Answer 1

SELECT  *
FROM    (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY productName ORDER BY price) AS rn
        FROM    mytable
        ) q
WHERE   rn = 1