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 FruitsAs 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
精彩评论