Select Max record !Help!


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]
            ,row_number() over(partition by [year]
                                   order by app_num desc
             as rn
        from ifta_return
select [year]
  from cte
 where rn=1


i tried to do this

hope it helps


drop create data ...
drop table #abc 

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

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

select * from #abc 
;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,