Select TOP 4 Records For Each Group

  • Thread starter Thread starter Tirelle
  • Start date Start date
T

Tirelle

I have a query that I need to select the TOP 4 records for each distintive
group.

The layout:
2 Columns of data(Charge and ICV). Charge is an integer and ICV is a single.

I need to get the TOP 4 ICVs for each charge. How do I set Grouping and/or
Summing to do this?

Thank You
 
Thanks Allen... This is getting me there but It is giving me the same results
for each charge. When I change values so that the TOP 4 are not the same, I
get the top 3 or so. Suggestions? I only have 1 table so I left out DUPE but
I added DISTINCT. Here is statement:

SELECT DISTINCT qryICVLog.ChargeNumber, qryICVLog.FinalTCV
FROM qryICVLog
WHERE (((qryICVLog.FinalTCV) In (SELECT DISTINCT TOP 4 FinalTCV
FROM qryICVLog)))
GROUP BY qryICVLog.ChargeNumber, qryICVLog.FinalTCV
HAVING (((qryICVLog.FinalTCV) Is Not Null))
ORDER BY qryICVLog.ChargeNumber, qryICVLog.FinalTCV;
 
Still moving forward... This works better but only returning 3 of 4 for last
charge.

SELECT qryICVLog.ChargeNumber, qryICVLog.FinalTCV
FROM qryICVLog
WHERE (((qryICVLog.FinalTCV) In (SELECT TOP 4 FinalTCV
FROM qryICVLog
GROUP BY qryICVLog.ChargeNumber, qryICVLog.FinalTCV)))
GROUP BY qryICVLog.ChargeNumber, qryICVLog.FinalTCV
HAVING (((qryICVLog.FinalTCV) Is Not Null))
ORDER BY qryICVLog.ChargeNumber, qryICVLog.FinalTCV;

I removed Distinct from it all together
 
The subquery needs:
a) to alias the table:
FROM qryICVLog AS SomeOtherName

b) a WHERE clause so it matches the value from the main record.
 
Still moving in right direction but hitting road blocks. Here is what I
have...

SELECT qryICVLog.ChargeNumber, qryICVLog.FinalTCV
FROM qryICVLog
WHERE (SELECT TOP 4 FinalTCV
FROM qryICVLog AS Dupe
WHERE Dupe.ChargeNumber = qryICVLog.ChargeNumber
ORDER BY Dupe.ChargeNumber, Dupe.FinalTCV)
GROUP BY qryICVLog.ChargeNumber, qryICVLog.FinalTCV
HAVING (((qryICVLog.FinalTCV) Is Not Null))
ORDER BY qryICVLog.ChargeNumber, qryICVLog.FinalTCV;

Here is what I get...
error message..."At most one record can be returned by this subquery"
 
Nevermind... Thanks Allen!!! Once agin you came through I found the error in
that I did not include what I was looking for. Works Great!!!
 
After I made these changes... My report performance went down drastically in
that it takes forever to open. Any sugestions?
 
Well I thought it was working but after a closer look here is the issue...

If all of my value(atleast the TOP 4 are different, It works fine. If More
than 4 FinalTCV's are the same for a given charge, it returns only 1 value
which may be the same for the TOP 4. here is the statement...
SELECT qryICVLog.ChargeNumber, qryICVLog.FinalTCV
FROM qryICVLog
WHERE qryICVLog.FinalTCV IN (SELECT TOP 4 FinalTCV
FROM qryICVLog AS Dupe
WHERE Dupe.ChargeNumber = qryICVLog.ChargeNumber
ORDER BY Dupe.ChargeNumber, Dupe.FinalTCV)
GROUP BY qryICVLog.ChargeNumber, qryICVLog.FinalTCV
HAVING (((qryICVLog.FinalTCV) Is Not Null))
ORDER BY qryICVLog.ChargeNumber, qryICVLog.FinalTCV;
 
Well I can't see your actual data, but it looks like you are GROUPing in the
main query, but not in the subquery. So, the TOP 4 values returned by the
subquery could be duplicates, which disappear when the main query does its
GROUPing?
 
OK. Here is what I get for the following statement. There is only 1 charge
in the tables with a range of different FinalICV's.

SELECT qryICVLog.ChargeNumber, qryICVLog.FinalTCV
FROM qryICVLog
WHERE (((qryICVLog.FinalTCV) In (SELECT TOP 4 FinalTCV

FROM qryICVLog AS Dupe
WHERE Dupe.ChargeNumber = qryICVLog.ChargeNumber
ORDER BY Dupe.FinalTCV, Dupe.ChargeNumber)))
GROUP BY qryICVLog.ChargeNumber, qryICVLog.FinalTCV
HAVING (((qryICVLog.FinalTCV) Is Not Null))
ORDER BY qryICVLog.ChargeNumber, qryICVLog.FinalTCV;

Results:
ChargeNumber FinalTCV
35 2.7
 
See:
Subquery basics: TOP n records per group
at:
http://allenbrowne.com/subquery-01.html#TopN
Click to expand...

That example uses a correlated subquery, which can be a very slow
performer. And it's the fact that it's a correlated subquery that
necessitates the alias in the subquery, so you can refer to values
in the instance of the table in the parent SQL statement and in the
sub-statement.
 
Access is usually slower with subqueries than other methods.
Click to expand...

Not all subqueries, but correlated subqueries in particular as slow,
because the subquery has to be executed once for each of the values
that links it to the parent query (which may be once for each record
of the parent query).
 
Perhaps something like this:

SELECT qryICVLog.ChargeNumber, qryICVLog.FinalTCV
FROM qryICVLog
WHERE qryICVLog.FinalTCV IN
(SELECT TOP 4 FinalTCV
FROM qryICVLog AS Dupe
WHERE (Dupe.ChargeNumber = qryICVLog.ChargeNumber)
AND (Dupe.FinalTCV Is Not Null)
GROUP BY Dupe.FinalTCV
ORDER BY Dupe.FinalTCV)
GROUP BY qryICVLog.ChargeNumber, qryICVLog.FinalTCV
ORDER BY qryICVLog.ChargeNumber, qryICVLog.FinalTCV;
 
Thanks Allen. That Worked. The query is only slow when opening the report?
Why is that? Are there any alternatives?
 
That Worked. The query is only slow when opening the report?
Why is that? Are there any alternatives?
Click to expand...

If the query opened by itself comes up quickly, and the report based
on that query takes a long time, these are the possible causes:

1. Page() of Pages() in the footer/header, because the first page
can't display until the last page has been formated, since that's
how the Pages() value is calculated.

2. sorting or grouping in the report that is not present in the
source query.
 
Back
Top