SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Drop TABLE if EXISTS #temp
Create table #temp(ID int, Country VARCHAR(40), ItemCount int, DeliveryDate Date, Itemtype VARCHAR(40) )
insert #temp(id,Country,itemCount,DeliveryDate,Itemtype)
Select 
             3012111,'Dublin',     100, '01-01-2022', 'Head Gears'
union select 2012111,'Dublin',     200, '01-05-2022', 'Head Gears'
union select 2012112,'Australia',  300, '01-03-2022', 'Knee Pad'
union select 2012110,'Australia',  100, '01-04-2022', 'Head Gears'
union select 2012113,'Singapore',  150, '01-05-2022', 'Head Gears'
union select 2012114,'Singapore',  200, '01-07-2022', 'FootWear'
union select 2012116,'Brazil',     500, '01-08-2022', 'Head Gears'
union select 2012115,'Brazil',     300, '01-06-2022', 'Head Gears'
union select 2012117,'Indonesia',  150, '01-10-2022', 'Foot Wear'
9 rows affected

select * from #temp


ID
Country
ItemCount
DeliveryDate
Itemtype


2012110
Australia
100
2022-01-04
Head Gears

2012111
Dublin
200
2022-01-05
Head Gears

2012112
Australia
300
2022-01-03
Knee Pad

2012113
Singapore
150
2022-01-05
Head Gears

2012114
Singapore
200
2022-01-07
FootWear

2012115
Brazil
300
2022-01-06
Head Gears

2012116
Brazil
500
2022-01-08
Head Gears

2012117
Indonesia
150
2022-01-10
Foot Wear

3012111
Dublin
100
2022-01-01
Head Gears


Select X.Country, min(id) Id, X.ItemCount
from ( select Country, max(itemCount) itemCount
       from #temp
       group by Country) X
inner join #temp T
on X.Country = T.Country
and X.itemCount = T.ItemCount
group by X.Country, X.itemCount


Country
Id
ItemCount


Australia
2012112
300

Brazil
2012116
500

Dublin
2012111
200

Indonesia
2012117
150

Singapore
2012114
200


with t1 as (
select a.*
from #temp a
where a.country in (select b.country
                    from #temp b
                    group by b.country
                    having min(b.ItemCount) = a.ItemCount)), 
t2 as (select a.*
from #temp a
where a.country in (select b.country
                    from #temp b
                    group by b.country
                    having max(b.ItemCount) = a.ItemCount))
select t1.country, t1.id, t1.ItemCount, t1.DeliveryDate, t1.Itemtype
from t1
left join t2 on t1.country = t2.country
where t1.DeliveryDate < t2.DeliveryDate
and t1.Itemtype = 'Head Gears'
and t1.Itemtype = t2.Itemtype;


country
id
ItemCount
DeliveryDate
Itemtype


Brazil
2012115
300
2022-01-06
Head Gears

Dublin
3012111
100
2022-01-01
Head Gears

ID	Country	ItemCount	DeliveryDate	Itemtype
2012110	Australia	100	2022-01-04	Head Gears
2012111	Dublin	200	2022-01-05	Head Gears
2012112	Australia	300	2022-01-03	Knee Pad
2012113	Singapore	150	2022-01-05	Head Gears
2012114	Singapore	200	2022-01-07	FootWear
2012115	Brazil	300	2022-01-06	Head Gears
2012116	Brazil	500	2022-01-08	Head Gears
2012117	Indonesia	150	2022-01-10	Foot Wear
3012111	Dublin	100	2022-01-01	Head Gears