CODEWITHSUNDEEP
sqlsql-servergreatest-n-per-group
Adrian Warness
Adrian Warness

Reputation: 69

SQL Server - Only Select Latest Date

RDBMS = Microsoft SQL Server

I work for a refrigeration company and we want to do a better job of tracking the cost bottles of refrigerant were bought at for each inventory location. I am trying to create a SQL Query that pulls this information but I am running into some issues. For each inventory location I want to display the last cost refrigerant was bought at for that inventory location.I want to see the latest date we have record of for this location purchasing a specific refrigerant. I have tried using the Max function unsuccessfully and the Row_Number function I have not been able to get work. Any help would be much appreciated.

See below the code sample I am trying to only get to display the Latest Date each inventory location purchased R-22 30 pound jug.

select 
    lctn_id as Location,
    invntryitm_id as InventoryItemID,
    invntryitm_nme as InventoryItemName,
    prchseordrlst_dte_rqstd as DateRequested,
    prchseordrlst_unt_cst as UnitCost
from 
    invntryitm
join
    prchseordrlst on prchseordrlst.invntryitm_rn = invntryitm.invntryitm_rn
join 
    prchseordr on prchseordr.prchseordr_rn = prchseordrlst.prchseordr_rn
join
    lctn on lctn.lctn_rn = prchseordr.lctn_rn
where   
    invntryitm.invntryitm_nme ='REFRIGERANT R-22 30#'
    and lctn_obslte = 'N'
group by 
    lctn.lctn_id, invntryitm.invntryitm_id, invntryitm.invntryitm_nme, 
    prchseordrlst.prchseordrlst_unt_cst
order by 
    lctn_id

Upvotes: 1

Views: 191

Answers (1)

Hambone
Hambone

Reputation: 16407

I think an analytic/windowing function would give you what you need:

with location_data as (
  select 
    lctn_id as Location,
    invntryitm_id as InventoryItemID,
    invntryitm_nme as InventoryItemName,
    prchseordrlst_dte_rqstd as DateRequested,
    prchseordrlst_unt_cst as UnitCost,
    max (prchseordrlst_dte_rqstd) over (partition by lctn_id) as max_date
  from
    invntryitm
    JOIN prchseordrlst on prchseordrlst.invntryitm_rn = invntryitm.invntryitm_rn
    JOIN prchseordr on prchseordr.prchseordr_rn = prchseordrlst.prchseordr_rn
    JOIN lctn on lctn.lctn_rn = prchseordr.lctn_rn
  where
    invntryitm.invntryitm_nme ='REFRIGERANT R-22 30#' and
    lctn_obslte = 'N'
)
select *
from location_data
where max_date = DateRequested
order by Location

Bear in mind that if there is a tie, two location_id records with the same date, then you will get both of them back. If this is an issue, then you probably want row_number() instead of max():

row_number() over (partition by lctn_id order by prchseordrlst_dte_rqstd desc) as rn

And then you would

where rn = 1

to get the first row

The reason I didn't list row_number() first is that max is O(n), and if your data has dates and times, it may be sufficient for what you need.

Upvotes: 2

Related Questions