Select Max record !Help!

Hello

I am trying to select the Max record between irt_Serial 95365 and 95366. The criteria is to select max app_num when the year and the quarter are the same. Also, both records have the same lic_serial. I want to select all the records records below except irt_Serial 95365. Below is my code and datasets. Please let me know if any questions.

-- creates a temp table to hold the highest ifta_return.app_num (by year/quarter)
If OBJECT_ID('tempdb..#tmpmaxgroup1') IS NOT NULL DROP TABLE #tmpmaxgroup1
select year, quarter, lic_serial, fivg_serial, irt_serial, max (app_num) as max_type_app_num
into #tmpmaxgroup1
from ifta_return IFTA
where lic_serial = '25531'
group by year, quarter, lic_serial, fivg_serial, irt_serial

year quarter lic_serial fivg_serial irt_serial max_type_app_num
2005 1 25531 165110 84523 1
2005 2 25531 175090 89760 1
2005 3 25531 185787 95365 1
2005 3 25531 185790 95366 2
2005 4 25531 196653 101069 1

If I understod your requirements correct, this might work:

with cte
  as (select [year]
            ,[quarter]
            ,lic_serial
            ,fivg_serial
            ,irt_serial
            ,app_num
            ,row_number() over(partition by [year]
                                           ,[quarter]
                                           ,lic_serial
                                   order by app_num desc
                              )
             as rn
        from ifta_return
     )
select [year]
      ,[quarter]
      ,lic_serial
      ,fivg_serial
      ,irt_serial
      ,app_num
  from cte
 where rn=1
;

Hi

i tried to do this

hope it helps

:slight_smile:
:slight_smile:

drop create data ...
drop table #abc 
go 

create taBLE #abc
(
year	int null,
quarter	int null,
lic_serial	int null,
fivg_serial	int null,
irt_serial	int null,
max_type_app_num int null
)
go 

insert into #abc select 2005,1,25531,165110,84523	,1
insert into #abc select 2005,2,25531,175090,89760	,1
insert into #abc select 2005,3,25531,185787,95365	,1
insert into #abc select 2005,3,25531,185790,95366	,2
insert into #abc select 2005,4,25531,196653,101069	,1
go 

select * from #abc 
go
SQL
;WITH cte 
     AS (SELECT * 
         FROM   #abc 
         WHERE   irt_serial > 95365 AND irt_serial  <= 95366) 
SELECT Max(max_type_app_num) 
FROM   cte 
GROUP  BY year, 
          quarter, 
          lic_serial

image