Get top 20 from aggregated records??

Hello
I'm using Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on
Windows NT 5.0 (Build 2195: Service Pack 4)
I have a big problem getting the right result from the this example
table...
AgentID | Destination
----
10 | BKK
10 | BKK
10 | BKK
10 | LON
10 | BEG
10 | BEG
10 | SJJ
10 | NYC
96 | BKK
96 | BKK
96 | BKK
96 | BKK
96 | BKK
96 | LON
96 | LON
96 | LON
96 | LON
96 | BEG
96 | BEG
96 | BEG
96 | SJJ
96 | SJJ
96 | MRU
96 | MRU
1000 | BKK
1000 | BKK
1000 | BKK
1000 | LON
1000 | BEG
1000 | HAN
1000 | HKG
1000 | ZAG
1000 | BLX
The result I need to get out of this table is top 5 destinations for
every agent id used here. Can someone please help with this?
Thx guys.
Miroslav Ostojicyou mean top 5 distinct destinations?
or just top 5. If just top5, then in what order?|||If you are searching for distinct destination, then you can do it this way..
select a.agentid, a.Destination, count(distinct b.Destination)
as con from tbl1 a, tbl1 b
where a.agentid = b.agentid and a.Destination <= b.Destination
group by a.agentid, a.Destination
having count(distinct b.destination) <= 5
order by a.agentid|||You left everyone guessing about a lot of things, particularly what
you are ranking on to determine what is top.
My guess is that this query
SELECT AgentID, Destination, count(*) as rows
FROM Example
GROUP BY AgentID, Destination
ORDER BY 1, 3 desc
which counts how many occurances of each Destination there are for an
AgentID, might be the basis for the ranking. If that is correct you
might see what this does for you:
SELECT AgentID, Destination, count(*) as rows
FROM Example
WHERE Destination IN
(SELECT TOP 5 Destination
FROM Example as X2
WHERE X1.AgentID = X2.AgentID
GROUP BY Destination
ORDER BY count(*) desc)
GROUP BY AgentID, Destination
ORDER BY AgentID, count(*) desc
Roy Harvey
Beacon Falls, CT
On 7 Jun 2006 02:02:06 -0700, miroslav.ostojic@.gmail.com wrote:

>Hello
>I'm using Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
>14:22:05
>Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on
>Windows NT 5.0 (Build 2195: Service Pack 4)
>I have a big problem getting the right result from the this example
>table...
> AgentID | Destination
>----
> 10 | BKK
> 10 | BKK
> 10 | BKK
> 10 | LON
> 10 | BEG
> 10 | BEG
> 10 | SJJ
> 10 | NYC
> 96 | BKK
> 96 | BKK
> 96 | BKK
> 96 | BKK
> 96 | BKK
> 96 | LON
> 96 | LON
> 96 | LON
> 96 | LON
> 96 | BEG
> 96 | BEG
> 96 | BEG
> 96 | SJJ
> 96 | SJJ
> 96 | MRU
> 96 | MRU
> 1000 | BKK
> 1000 | BKK
> 1000 | BKK
> 1000 | LON
> 1000 | BEG
> 1000 | HAN
> 1000 | HKG
> 1000 | ZAG
> 1000 | BLX
>
>The result I need to get out of this table is top 5 destinations for
>every agent id used here. Can someone please help with this?
>Thx guys.
>Miroslav Ostojic|||There is no field available in your table to order by.
You need to have some field by which we can sort it.
"miroslav.ostojic@.gmail.com" wrote:

> Hello
> I'm using Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
> 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on
> Windows NT 5.0 (Build 2195: Service Pack 4)
> I have a big problem getting the right result from the this example
> table...
> AgentID | Destination
> ----
> 10 | BKK
> 10 | BKK
> 10 | BKK
> 10 | LON
> 10 | BEG
> 10 | BEG
> 10 | SJJ
> 10 | NYC
> 96 | BKK
> 96 | BKK
> 96 | BKK
> 96 | BKK
> 96 | BKK
> 96 | LON
> 96 | LON
> 96 | LON
> 96 | LON
> 96 | BEG
> 96 | BEG
> 96 | BEG
> 96 | SJJ
> 96 | SJJ
> 96 | MRU
> 96 | MRU
> 1000 | BKK
> 1000 | BKK
> 1000 | BKK
> 1000 | LON
> 1000 | BEG
> 1000 | HAN
> 1000 | HKG
> 1000 | ZAG
> 1000 | BLX
>
> The result I need to get out of this table is top 5 destinations for
> every agent id used here. Can someone please help with this?
> Thx guys.
> Miroslav Ostojic
>