CODEWITHSUNDEEP
sqlsql-server-2005t-sql
Raihan Iqbal
Raihan Iqbal

Reputation: 407

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

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

Upvotes: 5

Views: 386

Answers (1)

Quassnoi
Quassnoi

Reputation: 425823

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

Upvotes: 2

Related Questions