开发者

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

开发者 https://www.devze.com 2022-12-21 15:59 出处:网络
For my ques开发者_如何学Pythontion lets consider the following sample table data: ProductID    ProductName    Price  Category

For my ques开发者_如何学Pythontion 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


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

精彩评论

暂无评论...
验证码 换一张
取 消