Top 5, for each

[Guest]

in my simple table, each row represents a sale. among the fields, are the
id# and the state.

id# state
2 OH
2 MI
3 MI
6 RI
2 OH
2 MI
3 RI
1 RI
1 OH

I need a query to list top 5 sales by id# by state.

So far, I am doing basically this:

SELECT state, id#, count(id#)
from TABLE
GROUP BY state, id#

to get the # sales by id# and by state; or

SELECT TOP 5 state, id#, count(id#)
from TABLE
WHERE state = parameter
group by state, id#

to get the top 5 for a given state, but I cannot seem to figure out how to
generate a list of the top 5 for each state.

Thanks for any help

[hope this isn't a duplicate--my last post seemed to fail]

 

[Guest]

For a Top query to work you have to have sort column. This is the value it
uses to determine what the Top values are. In this example though ID appears
to be a label and not a value so I am not sure if you want the Top 5 by ID.
You probably want to sort by a sale value or some similar field to determine
your Top 5 sales (i.e. largest sale values) and LIST them by ID and State.

 

[Marshall Barton]

in my simple table, each row represents a sale. among the fields, are the
id# and the state.

id# state
2 OH
2 MI
3 MI
6 RI
2 OH
2 MI
3 RI
1 RI
1 OH

I need a query to list top 5 sales by id# by state.

So far, I am doing basically this:

SELECT state, id#, count(id#)
from TABLE
GROUP BY state, id#

to get the # sales by id# and by state; or

SELECT TOP 5 state, id#, count(id#)
from TABLE
WHERE state = parameter
group by state, id#

to get the top 5 for a given state, but I cannot seem to figure out how to
generate a list of the top 5 for each state.

Try this kind of thing:

SELECT T.state, T.[id#], Count([id#])
FROM Table As T
WHERE T.state & T.[id#] IN
(SELECT TOP 5 X.state & X.[id#]
FROM Table As X
GROUP BY X.state & X.[id#]
ORDER BY Count(*) DESC
)
GROUP BY T.state, T.[id#]