Consider this mock up re show the top 3 most observed Plate Numbers and count of observations.
Table Plates
Code:
ID |
plate |
obsDate |
1 |
ABC234 |
23-May-2016 |
2 |
XRY888 |
23-May-2016 |
3 |
ABC234 |
24-May-2016 |
4 |
XRY888 |
27-May-2016 |
5 |
ABC234 |
25-May-2016 |
6 |
ABC234 |
28-May-2016 |
7 |
ABC234 |
29-May-2016 |
8 |
ABC234 |
30-May-2016 |
9 |
ABC234 |
31-May-2016 |
10 |
ZMT776 |
29-May-2016 |
11 |
QED123 |
28-May-2016 |
12 |
QED123 |
03-Jun-2016 |
13 |
QED123 |
04-Jun-2016 |
14 |
XRY888 |
04-Jun-2016 |
15 |
XRY888 |
05-Jun-2016 |
16 |
XRY888 |
07-Jun-2016 |
17 |
ABC234 |
07-Jun-2016 |
18 |
ABC234 |
08-Jun-2016 |
19 |
ABC234 |
09-Jun-2016 |
20 |
XRY888 |
10-Jun-2016 |
Query:
Code:
SELECT top 3 Plates.plate
, Count(Plates.obsDate) AS CountOfobsDate
FROM Plates
GROUP BY Plates.plate
ORDER BY Count(Plates.obsDate) DESC;
Result:
Code:
plate |
CountOfobsDate |
ABC234 |
10 |
XRY888 |
6 |
QED123 |
3 |