Row Number

Question

Row Number

ByronOne

SSCarpal Tunnel

Points: 4656
More actions
June 23, 2014 at 1:40 pm

#287145

Hi everyone
I hope someone can help
I have the following query which uses Row_Number
WITH A1
as
(
SELECT
,ID
,DATE
,NAME
,STATUS
,PRODUCT
,RowNum = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATE desc)
FROM Table1
)
SELECT * FROM A1
I would like to bring back the last DATE (Max) for each ID in the table A1. Any ideas?
There are many records for each ID but I'm only interested in seeing the last product bought ie on the last DATE in the database.
Thanks in advance.
BO

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

djj SSCoach Points: 18831 More actions · Answer 1

ByronOne (6/23/2014)
Hi everyone
I hope someone can help
I have the following query which uses Row_Number
WITH A1
as
(
SELECT
,ID
,DATE
,NAME
,STATUS
,PRODUCT
,RowNum = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATE desc)
FROM Table1
)
SELECT * FROM A1
I would like to bring back the last DATE (Max) for each ID in the table A1. Any ideas?
There are many records for each ID but I'm only interested in seeing the last product bought ie on the last DATE in the database.
Thanks in advance.
BO

You need a WHERE RowNum = 1

The code ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATE desc) puts a number on each row based on ID sorted by date desc so the "max" date is row 1 for each ID.

ByronOne SSCarpal Tunnel Points: 4656 More actions · Answer 2

Thanks for such a quick reply djj

I should have realised thst was the solution!

Much appreciated.

🙂