Top count

[SQLScholar]

Hey,

I have this code which is to select the top 20 insurers by gross.

SELECT Top 20
	count(*),
	[TOP].[Insurer Name] AS [Insurer Name],
	sum([TOP].Gross) AS Gross
FROM
	[TOP]

GROUP BY [TOP].[Insurer Name]
ORDER BY sum([TOP].Gross) desc

Problem i want "count(*)," to count the top 1 to 20, but i cant get this to work properly.

Any ideas

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[PHV]

You have to "rank" your Top 20 query.
Say you have a saved query named qryTop20gross:
SELECT Top 20 [Insurer Name], Sum(Gross) AS Gross
FROM [TOP]
GROUP BY [Insurer Name]
ORDER BY Sum(Gross) DESC;
You may try this query:
SELECT (Select Count(*) FROM qryTop20gross B WHERE B.Gross <= A.Gross) AS Rank, A.[Insurer Name], A.Gross
FROM qryTop20gross A
ORDER BY 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SQLScholar]

Having trouble getting this to work, seems to lock my third party peice of software. This subquery... there must be an easier way.

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[SQLScholar]

Aha got it - running abit slow though.

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[PHV]

Another way:
SELECT Count(*) AS Rank, A.[Insurer Name], A.Gross
FROM qryTop20gross A INNER JOIN qryTop20gross B ON A.Gross >= B.Gross
GROUP BY A.[Insurer Name], A.Gross
ORDER BY 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244