Selecting "most recent" records

Question

Selecting "most recent" records

lionfan91

SSCrazy Eights

Points: 8794
More actions
February 4, 2005 at 9:51 am

#66448

I'm sure this'll turn out to be an easy one but I'm kind of stumped. I have a table that keeps track of the most recent "item names" for items, which may change month to month. The table has 3 fields - code, date, and item name. For example, I may have:
AAA   Nov-04  ITEM_AAA_NAME
AAA   Dec-04  ITEM_AAA_NAME
AAA   Jan-05  ITEM_AAA_NAME_MOD
BBB   Nov-04  ITEM_BBB_NAME
BBB   Dec-04  ITEM_BBB_NAME_MOD
BBB   Jan-05  ITEM_BBB_NAME_MOD
CCC   Nov-04  ITEM_CCC_NAME
CCC   Dec-04  ITEM_CCC_NAME
CCC   Jan-05  ITEM_CCC_NAME
I need a query that returns the most recent name for all items. The results for the data above would be:
AAA   ITEM_AAA_NAME_MOD
BBB   ITEM_BBB_NAME_MOD
CCC   ITEM_CCC_NAME
I really don't care when the name changed but if the date can be (or has to be) a field in the resultset that's cool with me. Any ideas? TIA... Steve
PS -- the data file right now has approx 33K records for 3K specific items and grows by about 3K records per month.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

SELECT MAX( Date) and GROUP ON Code and Item Name, (if you need Item Name). You said the Item Name might change, so you may want to get that value through a sub-select.

I wasn't born stupid - I had to study.

lionfan91 SSCrazy Eights Points: 8794 More actions · Answer 2

I forgot one important part... items may be deleted but still need to be included in the resultset. So modifying my dataset a bit...

AAA Nov-04 ITEM_AAA_NAME

AAA Dec-04 ITEM_AAA_NAME

AAA Jan-05 ITEM_AAA_NAME_MOD

BBB Nov-04 ITEM_BBB_NAME

BBB Dec-04 ITEM_BBB_NAME_MOD

BBB Jan-05 ITEM_BBB_NAME_MOD

CCC Nov-04 ITEM_CCC_NAME

CCC Dec-04 ITEM_CCC_NAME

DDD Nov-04 ITEM_DDD_NAME

DDD Dec-04 ITEM_DDD_NAME

DDD Jan-05 ITEM_DDD_NAME

...needs to return the following:

AAA ITEM_AAA_NAME_MOD

BBB ITEM_BBB_NAME_MOD

CCC ITEM_CCC_NAME

DDD ITEM_DDD_NAME

Note that item CCC doesn't appear in the Jan-05 data, so I can't just find the max date in the table and retrieve all corresponding records. Sorry for forgetting that key point!

PW-201837 SSC-Insane Points: 20805 More actions · Answer 3

You need a sub-query to find max date by product code.

Then you need to join to that to pull the most recent BY PRODUCT

Select *

From YourProductTable As t

Inner Join

(

Select ProductCode, Max(Date) As MostRecent

From YourProductTable

Group By ProductCode

) vtable

On vtable.ProductCode = t.ProductCode And

vtable.MostRecent = t.Date

lionfan91 SSCrazy Eights Points: 8794 More actions · Answer 4

lionfan91

SSCrazy Eights

Points: 8794

February 4, 2005 at 10:18 am

#540736

Sweet! Thanks PW, that seemed to do the trick.