Finding the highest of a count query

[k0r54]

Hi,

I am using this sql query : -

SELECT Monthly_below_report.Device
FROM Monthly_below_report
GROUP BY Monthly_below_report.Device, Monthly_below_report.[FS-ID]
HAVING (((Monthly_below_report.[FS-ID])=780));

The problem is this currently pulls out 5 records (all devices found and the amount of times they appear).

How can i get it to pull out JUST the highest count.

I.E :-
that query above will pull out
15
12
10
3
1

How can i get it just to display 15.

Thanks
k0r54

 

[Timtropolis]

Hi Kor,

Have you tried using the MAX function? That should get you the desired result you need.

Tim

 

[k0r54]

I have tried that and dmax but it still wont work, well dmax works but it needs to be in a different query, which i REALLY want to avoid.

 

[RuralGuy]

I'm not a wiz at SQL but it seems as though you could make use of ORDER BY xxx DESC and the DISTINCT clause.

 

[k0r54]

Yeah, i also tried that, but unlike MySQL you can't put a LIMIT to just 1 record. Otherwise i could desc and just take the first record.

 

[RuralGuy]

How about SELECT TOP 1?

 

[k0r54]

well i didn't know about that lol

Thanks very much