Select a latest date record group by certain column

Transact-SQL
1

hi all,

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

SQL_MaxDate
SQL_MaxDate927×529 22.7 KB

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

2

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

3

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

image
image952×253 5.31 KB

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

image
image974×165 3.21 KB

4

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

5

Not sure I understand what you mean

Can you please explain

I thought I gave latest date for package id

6

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..

7

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

8

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.