top 10 records per date

[mohsin via AccessMonster.com]

Hi expert

I'm using below queries to output the top10 records per date,
Need your help, to correct it since not work..


SELECT [A].MSS, [A].Date, [A].[CC%]
FROM [A] As [A1]
WHERE [A].Date AND [A].[CC%] IN
(SELECT TOP 10 [A].[CC%]
FROM [A]
WHERE [A1].Date
ORDER BY [A].[CC%] DESC)
ORDER BY [A].Date, [A].[CC%]

thannk you for your help
-Mohsin

 

[John Spencer MVP]

What you have posted has many errors in it. I really doubt that this is a
query you have. I suspect this is either homework or you have entered the
query by typing it.

Better to copy and paste the actual SQL and then clean it up in your posting.
Giving you the benefit of the doubt and assuming this is not homework, you
would need that query to read

SELECT [A1].MSS
, [A1].[Date]
, [A1].[CC%]
FROM [A] As [A1]
WHERE [A1].[CC%] IN
(SELECT TOP 10 [A].[CC%]
FROM [A]
WHERE [A].[Date] = [A1].[Date]
ORDER BY [A].[CC%] DESC)
ORDER BY [A1].Date, [A1].[CC%]

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

 

[mohsin via AccessMonster.com]

Hi John

Thanks John,
Basically the actual table name is long, and I replacing A as input table and
A1 as output table

The queries take a bit long, and seems stuck,,,

SELECT [A1].MSS, [A1].[Date], [A1].[CC%]
FROM [A] As [A1]
WHERE [A1].[CC%] IN
(SELECT TOP 10 [A].[CC%]
FROM [A]
WHERE [A].[Date] = [A1].[Date]
ORDER BY [A].[CC%] DESC)
ORDER BY [A1].Date, [A1].[CC%];

What you have posted has many errors in it. I really doubt that this is a
query you have. I suspect this is either homework or you have entered the
query by typing it.

Better to copy and paste the actual SQL and then clean it up in your posting.
Giving you the benefit of the doubt and assuming this is not homework, you
would need that query to read

SELECT [A1].MSS
, [A1].[Date]
, [A1].[CC%]
FROM [A] As [A1]
WHERE [A1].[CC%] IN
(SELECT TOP 10 [A].[CC%]
FROM [A]
WHERE [A].[Date] = [A1].[Date]
ORDER BY [A].[CC%] DESC)
ORDER BY [A1].Date, [A1].[CC%]

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

Hi expert

[quoted text clipped - 12 lines]

thannk you for your help
-Mohsin

 

[John Spencer MVP]

If you have a lot of records then this will be slow. It is a correlated
subquery and that means the query in the where clause runs once for every
record in your database.

There is a second method using a ranking query that may be faster. That looks
like the following. Be aware that I sometimes mess up the > or < sign usee in
the FROM clause and so you may need to change that comparison to get the top
10 instead of the bottom 10. Also, this will return TIES for the last position.

SELECT A1.MSS, A1.Date, A1.[CC%]
FROM A as A1 LEFT JOIN A as A2
ON A1.[Date] = A2.[Date]
AND A1.[CC%] > A2.[CC%]
GROUP BY A1.MSS, A1.Date, A1.[CC%]
HAVING Count(A2.[Date]) < 9


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

Hi John

Thanks John,
Basically the actual table name is long, and I replacing A as input table and
A1 as output table

The queries take a bit long, and seems stuck,,,

SELECT [A1].MSS, [A1].[Date], [A1].[CC%]
FROM [A] As [A1]
WHERE [A1].[CC%] IN
(SELECT TOP 10 [A].[CC%]
FROM [A]
WHERE [A].[Date] = [A1].[Date]
ORDER BY [A].[CC%] DESC)
ORDER BY [A1].Date, [A1].[CC%];

What you have posted has many errors in it. I really doubt that this is a
query you have. I suspect this is either homework or you have entered the
query by typing it.

Better to copy and paste the actual SQL and then clean it up in your posting.
Giving you the benefit of the doubt and assuming this is not homework, you
would need that query to read

SELECT [A1].MSS
, [A1].[Date]
, [A1].[CC%]
FROM [A] As [A1]
WHERE [A1].[CC%] IN
(SELECT TOP 10 [A].[CC%]
FROM [A]
WHERE [A].[Date] = [A1].[Date]
ORDER BY [A].[CC%] DESC)
ORDER BY [A1].Date, [A1].[CC%]

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

Hi expert

[quoted text clipped - 12 lines]

thannk you for your help
-Mohsin