SQLTeam.com | Weblogs | Forums

Select a latest date record group by certain column

hi all,

i would like to get a latest date records group by certain column.

i only want to get the yellow highlighted records which group by A.PackageRateId, only get one latest date records for the distinct A.PackageRateId

my sample query:

select distinct A.PackageRateId, A.ID, max(A.StopDate) OVER(partition BY A.PackageRateId ORDER BY A.StopDate ASC)
FROM EPOS.dbo._EPOS_PackageRateItems as A with (nolock)
where
--A.PackageRateId = 2079 and
a.enabled = 1 and GETDATE() <= A.StopDate

hi

I tried to do this

Hope this helps :slight_smile: :slight_smile:

please click arrow to the left for Drop Create Data ..
drop table EPOS_PackageRateItems
go 

create table EPOS_PackageRateItems
(
PackageRateId int ,
ID int, 
Stop_Date date 
)
go

insert into  EPOS_PackageRateItems select 2079 , 5729 , '2019-12-19'
insert into  EPOS_PackageRateItems select 2079 , 5730 , '2019-12-20'
insert into  EPOS_PackageRateItems select 2079 , 5739 , '2019-12-21'
go

please click arrow to the left for SQL
; with cte_maxdate as 
(
select  PackageRateId
       ,MAX(Stop_date) as maxstopdate 
from EPOS_PackageRateItems
group by PackageRateId
)
select 'SQL Output', a.* 
from 
      EPOS_PackageRateItems a join cte_maxdate b 
          on  a.PackageRateId = b.PackageRateId 
          and Stop_Date       = maxstopdate
go

sorry, ID is unique key.
there is problem because packagerateID is not unique and will have duplicate records.

Not sure I understand what you mean

Can you please explain

I thought I gave latest date for package id

sorry, i miss out StartDate Column.

Refer image. i only want to show the highlighted yellow row with the latest stopdate group by packageRateID
image

startDate to StopDate duration may be one day, twodays, one months, etc..

So what if package rate id has duplicates

My solution gives max date
For each package rate id

Example

Package rate ID 3456. Max date 12/13/2019
Package rate ID 6785. Max date 12/6/2019

If we want max date for all records
Don't care abt any column

Top 1 order by date desc

Adjust the ORDER BY in the row_number as needed...

   With packageRateRanks
     As (
 Select a.PackageRateId
      , a.ID
      , rn = row_number() over(Partition By a.PackageRateId Order By a.ID, a.StopDate desc)
   From EPOS.dbo._EPOS_PackageRateItems As a With (nolock)
  Where --A.PackageRateId = 2079 and
        a.enabled = 1
    And getdate() <= a.StopDate
        )
 Select *
   From packageRateRanks prr
  Where prr.rn = 1;

Note: you should remove the NOLOCK as it can cause incorrect data/duplicate data - or even fail.