Top ten on a group by query

[Mugs]

I have created a query which has the fields - Alarm Type, which is sorted ascending, Node Name, and Alarm Count (By Node Name) which is sorted descending. The purpose of the query is to demonstrate the node which produced the highest type of each alarm. The query does this fine but, for my report I would like to just show the top 10 of each alarm type without having to split up the query at all. Is this possible??? Any help would be greatly appreciated.

 

[Psychpt]

in the sql view of the query change SELECT to SELECT TOP 10

 

[WilsonM]

You can do this by setting the first values to 10 on the your query properties.

To do this open your query in design mode and right click beside the table and open the properties window.

Remember the fields in your query have to be sorted in order for the system to figure out which ones are the top ones.

Good luck,

Wilson

 

[Mugs]

Cheers for trying to help gents but, both of those ways I already know and that is not what I want to acheive. If I have 15 different alarm types, my aim is within my report and/or query to demonstrate the top ten of each alarm type but grouped by alarm type. So if two of my alarms were xxxx and yyyy then I would like to see the top ten alarm generating nodes for alarm type xxxx and yyyy as well as all of the other alarm types generated. Is this possible??

 

[caerulea]

Mugs:

Did you ever figure out the solution? I'm trying to do the same thing with my data, and I can't figure it out.

Cheers!

 

[tlbroadbent]

This one has been fun because it was very challenging. Yet the solution is much simpler than I had thought it would be. Here is a query that should work nicely.
[tt]
SELECT a.[Alarm Type], a.[Node Name], a.[Alarm Count]
FROM QryAlarmCount AS a
WHERE a.[Alarm Count] > 0
AND (SELECT count(*) FROM QryAlarmCount
WHERE [Alarm Count] > 0
AND [Alarm Type]=a.[Alarm Type]
AND [Alarm Count]>a.[Alarm Count]) < 10
ORDER BY a.[Alarm Type], a.[Alarm Count] DESC;[/tt] Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[LScott]

Additional Question:

I have a table with about 11,500 records, and the query worked great, but it took nearly 20 mins to run on a respectable machine. Is there any way to enhance the performance with large recordsets?

Thanks,
Scott