SQL: Compare 2 rows in the same table and output result when conditions are met-CodePudding

I am newbie to SQL, Apologies if this is a repeated type of question, tried searching for answers in the forum but couldn't find any answers. I have created a temp table as below:

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'

I am trying to write a query to output results from the temp table only when the following conditions are met:

Grouped by "Country, compare the Itemcount and delivery date
Return output only if the delivery date of the lowest itemcount for a given country by itemtype "Head Gears" is before the largest itemcount. If there is only one delivery for a given country by Itemtype "Head Gears", don't return anything as an output.

Tried this code but couldn't finish it to get the desired output:

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

This is the output I need: https://i.stack.imgur.com/3mHWD.png

Thanks for the help!

CodePudding user response：

I have assumed you are using SQL Server. Also, If I have understood your question then I think that your image for the output you need is not correct. I think that for the data you provided your result needs to be Dublin 100 and not Dublin 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;

I believe this can be written better but I hope this helps...

Here is the fiddle you where you can see the code in action: clickhere