Select top N, Group by query

[guytz72]

Hi All,

I am trying to return a result set that gives me the top 20 results for EACH group (PERILNAME in this case), not the top 20 results of the whole result set.

I have been able to create the resluts I want using UNION, but this is not practical when there is more than a few groups. Below is the code the returns the results I am after, any ideas how to achieve this another way?

SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT) AS [Total Value],
COUNT(loc.LOCID) AS [Num Locs],loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Earthquake'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
UNION
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT),
COUNT(loc.LOCID) ,loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Windstorm'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
UNION
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT),
COUNT(loc.LOCID) ,loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Tornado/Hail'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
UNION
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT),
COUNT(loc.LOCID) ,loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Flood'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
ORDER BY LookupPeril.PERILNAME, [Total Value]

TIA.

[emerkel]

Hi,

If you have or have access to the Guru's Guide to Transact SQL you will find the answer to your problem on page 125. I had to do exactly the same thing today that is why your question stood out.

Thanks

Eddie

[guytz72]

Sorry Eddie, I don't have that book but I will have a look for it and see if I can get it.

Any chance of posting the answer though?

Cheers,
Guytz

[zlp321002]

--try with this way
declare @t table(b varchar(10), c varchar(10), d int)
insert into @t select 'a','dd',1
union all select 'a','ee',3
union all select 'a','ff',5
union all select 'b','dd',1
union all select 'b','ee',2
union all select 'b','ff',6

select
t.*
from
@t t
where
t.c in(select top 2 c from @t where b=t.b order by d desc)
--result:
b c d
---------- ---------- -----------
a ee 3
a ff 5
b ee 2
b ff 6

[guytz72]

Nice one zlp321002 that worked.

Below is the code I used for anyone else needing to do this (Added Union to sum all values not in the top 20 as well)


SELECT LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT) AS [Total Value],
COUNT(loc.LOCID) AS [Num Locs],loccvg.VALUECUR
INTO #TOPTEMP
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR


select
t.PERILNAME,t.ACCGRPNUM,t.ACCGRPNAME,t.[Total Value],t.[Num Locs],t.VALUECUR
from
#TOPTEMP t
where
t.ACCGRPNUM in (select top 20 ACCGRPNUM from #TOPTEMP x where x.PERILNAME = t.PERILNAME)
Union
select PERILNAME, 'TOTAL', 'OTHER', SUM([Total Value]), SUM([Num Locs]), VALUECUR from #TOPTEMP
Where ACCGRPNUM not in (
select
t.ACCGRPNUM
from
#TOPTEMP t
where
t.ACCGRPNUM in (select top 20 ACCGRPNUM from #TOPTEMP x where x.PERILNAME = t.PERILNAME))
Group by PERILNAME, VALUECUR
order by 1,4 desc

Thanks again!!