Top 25 Query

[buddafish]

hey everyone,

i need to return the top 25 customers for each state. sounds simple enough, but it's not working for me...

i am working from a union select statement that joins current month table and remainder of 2004 months.


SELECT [UNION SELECT CURRENT YEAR].CURRENTSALES, [UNION SELECT CURRENT YEAR].CUSTNUMBER, [UNION SELECT CURRENT YEAR].STATE
FROM [UNION SELECT CURRENT YEAR]
ORDER BY [UNION SELECT CURRENT YEAR].CURRENTSALES DESC;


so i need the TOP 25 CURRENTSALES, with CUSTNUMBER and STATE.

Thanks in advance,

spot

 

[PHV]

top 25 customers for each state
Create a saved ranking query named, say, qryRankSales:
SELECT U.CURRENTSALES, U.CUSTNUMBER, U.STATE
,(SELECT Count(*) FROM [UNION SELECT CURRENT YEAR] R WHERE R.STATE=U.STATE And R.CURRENTSALES>=U.CURRENTSALES) AS Rank
FROM [UNION SELECT CURRENT YEAR] U;

And then your top 25 query may be coded like this:
SELECT CURRENTSALES, CUSTNUMBER, STATE
FROM qryRankSales
WHERE Rank<=25;

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

 

[dhookom]

You might be able to do this with a single query (with subquery)
SELECT U.CURRENTSALES, U.CUSTNUMBER, U.STATE
FROM [UNION SELECT CURRENT YEAR] U
WHERE U.CUSTNUMBER
IN (SELECT TOP 25 CUSTNUMBER
FROM [UNION SELECT CURRENT YEAR] N
WHERE N.STATE = U.STATE
ORDER BY CURRENTSALES DESC);

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[easyit]

See thread701-788031