please help to revamp this simple but ugly query

Question

I've done the query but it looks ugly. And the main concern, i worry the performance would be slow.

This is my table:

ID      ProductCode         Qty        PYear      PMonth
1            A1              4          2015        2
2            A2              5          2015        2  
3            A3              2          2014       12
4            A3             16          2015        4

I want to get the latest sell date categories by each product, below is the result that i want:

ProductCode         Qty         Year      Month
     A1              4          2015        2
     A2              5          2015        2  
     A3             16          2015        4

looks simple, but my query is complicated, i bet there must be an easy solution for this, below is my query i'm working with, i joined the year and month into 1 column:

SELECT A.ProductCode, B.Qty, PDate 
FROM (
    SELECT MAX(ID) AS purchaseID, 
    ProductCode, 
    MAX(CAST(PYear AS VARCHAR(4)) + '-' + CAST(PMonthAS VARCHAR(2)) + '-1') AS PDate
    FROM Table1
    GROUP BY ProductCode
) AS B
LEFT JOIN Table1 AS B ON A.ID= B.ID

"And the main concern, i worry the performance would be slow" Stop guessing start profiling. Anything else is a waste of time. — PeeHaa, May 18 '15 at 09:08
possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) — Peter Lang, May 18 '15 at 09:16
I would also recommend keeping a single Date column instead of year and month in different columns. — Zohar Peled, May 18 '15 at 10:15

score 2 · Accepted Answer · answered May 18 '15 at 10:12

Using windowed functions should do the trick - this gives each record a row based on descending date, and groups by productcode

SELECT 
purchaseID
,ProductCode
,Qty
,Pdate
FROM
( 
SELECT 
purchaseID 
,Qty
,ProductCode 
,CAST(PYear AS VARCHAR(4)) + '-' + CAST(PMonthAS VARCHAR(2)) + '-1') AS PDate
,ROW_NUMBER() OVER (PARTITION BY ProductCode ORDER BY CAST(PYear AS VARCHAR(4)) + '-' + CAST(PMonthAS VARCHAR(2)) + '-1') DESC) AS ROWNO
FROM Table1 
)
WHERE ROWNO = 1

score 2 · Answer 2 · answered May 18 '15 at 10:15

You could use ROW_NUMBER with a CTE? You don't really need to use a JOIN in this case:

;WITH CTE AS (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY PRODUCTCODE ORDER BY PYEAR DESC, PMONTH DESC) AS RN
  FROM TABLE1)
 SELECT PRODUCTCODE, QTY, PYEAR, PMONTH
 FROM CTE
 WHERE RN = 1

Here's an SQL Fiddle: http://sqlfiddle.com/#!6/90fe1/1

please help to revamp this simple but ugly query

2 Answers2