1

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?

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.