How to bring in the most recent record in SQL in Teradata

teradata

I have a table that contains store number, inventory $, inventory year from 2014 through 2018. so if store 111 has inventoried in all the years, I will have 5 records for that store:

Store Inventory year Inventory $
 111      2014         $$$$
 111      2015         $$$$
 111      2016         $$$$
 111      2017         $$$$
 111      2018         $$$$

and if store 121 has inventoried every year but 2018 I would have 4 records for that store:

Store Inventory year Inventory $
 111      2014         $$$$
 111      2015         $$$$
 111      2016         $$$$
 111      2017         $$$$

I would like to write a query that would bring one record per store, and only the one with the most recent inventory – so based on my above example, I would have the 2018 inventory result for store 111 and 2017 inventory result for store 121.

Can you help please?

Best Answer

This is a ranking task, easily done based on RANK/ROW_NUMBER.

select ...
from 
 ( select ...
      ,rank() over (partition by Store order by Inventory_year desc) as rn
   from tab
 ) as dt
where rn = 1

Teradata supports an SQL extension to avoid nesting:

select *
from tab
QUALIFY rank() over (partition by Store order by Inventory_year desc) = 1
Related Question