Is there any query to only return 1 row for each product-CodePudding

I want to make a query on my data and only want to return 1 row for each product name where date is bigger.

Data:

╔═══════════╦═════════════╦═══════╦═══════╗
║ ProductID ║ ProductName ║ Total ║ date  ║
╠═══════════╬═════════════╬═══════╬═══════╣
║      1001 ║ abc         ║    12 ║  2    ║
║      1002 ║ abc         ║    23 ║  4    ║
║      2002 ║ xyz         ║     8 ║  5    ║
║      3004 ║ ytp         ║    15 ║  1    ║
║      4001 ║ aze         ║    19 ║  1    ║
╚═══════════╩═════════════╩═══════╩═══════╝

I mean I want to see this result:

╔═══════════╦═════════════╦═══════╦═══════╗
║ ProductID ║ ProductName ║ Total ║ date  ║
╠═══════════╬═════════════╬═══════╬═══════╣
║      1002 ║ abc         ║    23 ║  4    ║
║      2002 ║ xyz         ║     8 ║  5    ║
║      3004 ║ ytp         ║    15 ║  1    ║
║      4001 ║ aze         ║    19 ║  1    ║
╚═══════════╩═════════════╩═══════╩═══════╝

CodePudding user response：

Try this :

SELECT DISTINCT ON (ProductName)
       ProductID, ProductName, Total, date
  FROM your_table
 ORDER BY ProductName, date DESC

CodePudding user response：

SELECT *FROM
(
  SELECT X.ProductID,X.ProductName,X.Total,X.DATE,
    ROW_NUMBER()OVER(PARTITION BY X.PRODUCTNAME ORDER BY X.DATE DESC)XCOL
  FROM YOUR_TABLE AS X
)A WHERE A.XCOL=1

CodePudding user response：

Try this :

   SELECT a.ProductID, a.ProductName, a.Total, a.date
    FROM Product a
    INNER JOIN (
        SELECT ProductName, MAX(date) date
        FROM Product
        GROUP BY ProductName
    ) b ON a.ProductName = b.ProductName AND a.date = b.date
    order by a.ProductID

http://sqlfiddle.com/#!17/7ffd5/1