SQL Server query - select only the first row of each product code

Question

I need to do a filter on the last purchase price for my list of products, but I need only the first row of each product (it is already on the correct order).

Example:

PRODUCT CODE                    PRICE      PURCH.DATE
-----------------------------------------------------
PO.MC.PER.001.N.PORTVIDEOCARD   0,3      20140521
PO.MC.PER.001.N.PORTVIDEOCARD   0,3      20140203
PO.MC.PER.001.N.PORTVIDEOCARD   0,3     20131115
PO.MC.PER.001.N.PORTVIDEOCARD   0,6     20130625
PO.MC.PER.001.N.PORTVIDEOCARD   0,5     20121122
SV.AI.SEI.006.N.CONFIEQUIPARE   800     20121211
SV.AI.SEI.006.N.CONFIEQUIPARE   3675    20121211
SV.AI.SEI.006.N.CONFIEQUIPARE   2000    20121114
SV.AI.SEI.006.N.CONFIEQUIPARE   2000    20120904
SV.AI.SFT.001.N.SOFTGERENCAME   2184    20121210
TI.AI.COP.001.N.NOTEBOOKPCXXX   3020,28 20110902
TI.AI.COP.002.N.COMPUTDESKTOP   2466,47 20140123
TI.AI.COP.002.N.COMPUTDESKTOP   2572    20131107
TI.AI.COP.002.N.COMPUTDESKTOP   2320    20131001
TI.AI.COP.002.N.COMPUTHP300GB   1517,5  20111130
TI.AI.COP.002.N.HPSMARTARRAYP   548,7   20111130
TI.AI.COP.002.N.UTRIUM920SAS    6050,6  20111130
TI.AI.COP.006.N.NOTEBOOKHPXXX   3020,28 20111117
TI.AI.COP.008.N.COMPUTINTELXE   1715    20111130
TI.AI.COP.009.N.COMPHP460WCSH   916,6   20111130
TI.AI.COP.011.N.MONITCOMPUTAD   25      20130613
TI.AI.COP.011.N.MONITCOMPUTAD   478     20130521

then I expect to have the following result:

PRODUCT CODE                    PRICE      PURCH.DATE
-----------------------------------------------------       
PO.MC.PER.001.N.PORTVIDEOCARD   0,3      20140521
SV.AI.SEI.006.N.CONFIEQUIPARE   800     20121211
SV.AI.SFT.001.N.SOFTGERENCAME   2184    20121210
TI.AI.COP.001.N.NOTEBOOKPCXXX   3020,28 20110902
TI.AI.COP.002.N.COMPUTDESKTOP   2466,47 20140123
TI.AI.COP.002.N.COMPUTHP300GB   1517,5  20111130
TI.AI.COP.002.N.HPSMARTARRAYP   548,7   20111130
TI.AI.COP.002.N.UTRIUM920SAS    6050,6  20111130
TI.AI.COP.006.N.NOTEBOOKHPXXX   3020,28 20111117
TI.AI.COP.008.N.COMPUTINTELXE   1715    20111130
TI.AI.COP.009.N.COMPHP460WCSH   916,6   20111130
TI.AI.COP.011.N.MONITCOMPUTAD   25      20130613

So, the final query will only show me the last price for EACH product, ONCE

I can't use distinct because they are already obviously distinct (although the product repeats, the price and/or purchase date don't)

I'm using SQL Server 2008 R2

score 1 · Answer 1 · answered Jul 24 '14 at 18:35

1

You can use the row_number function:

with CTE as
(select
[Product Code],
[Price],
[Purch.date],
row_number() over (partition by [product code] order by [purch.date] desc) as RN
from
<your table>
)

select * from CTE
where
RN = 1

answered Jul 24 '14 at 18:35

Andrew

8,445
3
28
46

score 1 · Accepted Answer · answered Jul 24 '14 at 18:36

1

See if it works:

 WITH cte AS
    (
       SELECT *,
             ROW_NUMBER() OVER (PARTITION BY PRODUCTCODE ORDER BY PURCHDATE DESC) AS rn
       FROM Table
    )
    SELECT *
    FROM cte
    WHERE rn = 1

answered Jul 24 '14 at 18:36

Bhasyakarulu Kottakota

823
1
6
10

thank you so much sir, it was exactly that. i didn´t knew the function row_number() and partition. always nice to learn. Cheers! – Luiz Carlos Jul 24 '14 at 19:50

SQL Server query - select only the first row of each product code

2 Answers2