Help with TOP 10 query please

[Bill]

Hi All,

Can somebody turn this into a top 10 query (pick the top ten values of the
count for each grouping) for the grouping please. I have tried to follow
Allen's advice on his website but the SQL seems to be beyond me!!!

Any help greatly appreciated.

Regards.
Bill

SELECT Hour([DDate]) AS HHour, [q_Table woth Dates and Times].Site,
Count([q_Table woth Dates and Times].Site) AS CountOfSite
FROM [q_Table woth Dates and Times]
GROUP BY Hour([DDate]), [q_Table woth Dates and Times].Site
ORDER BY Hour([DDate]), Count([q_Table woth Dates and Times].Site) DESC;

 

[KARL DEWEY]

This will work except if there are ties for tenth place both will be shown --

CountOfHours --
SELECT Site, Hour([DDATE]) AS HHour, Count(Site) AS QTY
FROM [q_Table woth Dates and Times]
GROUP BY Site, Hour([DDATE])
ORDER BY Site, Count(Site) DESC;

SELECT Q.Site, Q.HHour, Q.QTY, (SELECT COUNT(*) FROM [CountOfHours] Q1
WHERE Q1.[Site] = Q.[Site]
AND Q1.[QTY] >= Q.[QTY])+1 AS Rank
FROM CountOfHours AS Q
WHERE ((((SELECT COUNT(*) FROM [CountOfHours] Q1
WHERE Q1.[Site] = Q.[Site]
AND Q1.[QTY] >= Q.[QTY])+1)<=10))
ORDER BY Q.Site, Q.QTY DESC;

 

[Bill]

Thanks Karl, is that one or two queries. Can I cut and paste into the SQL
view?
Ta.
Bill.

This will work except if there are ties for tenth place both will be
shown --

CountOfHours --
SELECT Site, Hour([DDATE]) AS HHour, Count(Site) AS QTY
FROM [q_Table woth Dates and Times]
GROUP BY Site, Hour([DDATE])
ORDER BY Site, Count(Site) DESC;

SELECT Q.Site, Q.HHour, Q.QTY, (SELECT COUNT(*) FROM [CountOfHours] Q1
WHERE Q1.[Site] = Q.[Site]
AND Q1.[QTY] >= Q.[QTY])+1 AS Rank
FROM CountOfHours AS Q
WHERE ((((SELECT COUNT(*) FROM [CountOfHours] Q1
WHERE Q1.[Site] = Q.[Site]
AND Q1.[QTY] >= Q.[QTY])+1)<=10))
ORDER BY Q.Site, Q.QTY DESC;

Hi All,

Can somebody turn this into a top 10 query (pick the top ten values of
the
count for each grouping) for the grouping please. I have tried to follow
Allen's advice on his website but the SQL seems to be beyond me!!!

Any help greatly appreciated.

Regards.
Bill

SELECT Hour([DDate]) AS HHour, [q_Table woth Dates and Times].Site,
Count([q_Table woth Dates and Times].Site) AS CountOfSite
FROM [q_Table woth Dates and Times]
GROUP BY Hour([DDate]), [q_Table woth Dates and Times].Site
ORDER BY Hour([DDate]), Count([q_Table woth Dates and Times].Site) DESC;

 

[John Spencer MVP]

Save this query as q_TableSiteDateCount
SELECT Hour([DDate]) AS HHour
, [q_Table woth Dates and Times].Site
, Count([q_Table woth Dates and Times].Site) AS CountOfSite
FROM [q_Table woth Dates and Times]
GROUP BY Hour([DDate]), [q_Table woth Dates and Times].Site

Then you can use something like the following
SELECT Q1.HHour, Q1.Site, Q1.CountOfSite
FROM q_TableSiteDateCount as Q1 LEFT JOIN q_TableSiteDateCount as Q2
ON Q1.HHour = Q2.HHour
AND Q1.Site = Q2.Site
AND Q1.CountOfSite > Q2.CountOfSite
GROUP BY Q1.HHour, Q1.Site, Q1.CountOfSite
HAVING Count(CountOfSite) < 9

You may need to change the comparison operator in the FROM clause to < from >
in order to get the correct set of records.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

Thanks Karl, is that one or two queries. ------ 2

Thanks Karl, is that one or two queries. Can I cut and paste into the SQL
view?
Ta.
Bill.

This will work except if there are ties for tenth place both will be
shown --

CountOfHours --
SELECT Site, Hour([DDATE]) AS HHour, Count(Site) AS QTY
FROM [q_Table woth Dates and Times]
GROUP BY Site, Hour([DDATE])
ORDER BY Site, Count(Site) DESC;

SELECT Q.Site, Q.HHour, Q.QTY, (SELECT COUNT(*) FROM [CountOfHours] Q1
WHERE Q1.[Site] = Q.[Site]
AND Q1.[QTY] >= Q.[QTY])+1 AS Rank
FROM CountOfHours AS Q
WHERE ((((SELECT COUNT(*) FROM [CountOfHours] Q1
WHERE Q1.[Site] = Q.[Site]
AND Q1.[QTY] >= Q.[QTY])+1)<=10))
ORDER BY Q.Site, Q.QTY DESC;

Hi All,

Can somebody turn this into a top 10 query (pick the top ten values of
the
count for each grouping) for the grouping please. I have tried to follow
Allen's advice on his website but the SQL seems to be beyond me!!!

Any help greatly appreciated.

Regards.
Bill

SELECT Hour([DDate]) AS HHour, [q_Table woth Dates and Times].Site,
Count([q_Table woth Dates and Times].Site) AS CountOfSite
FROM [q_Table woth Dates and Times]
GROUP BY Hour([DDate]), [q_Table woth Dates and Times].Site
ORDER BY Hour([DDate]), Count([q_Table woth Dates and Times].Site) DESC;