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 |