Select TOP 4 Records For Each Group

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
 
T

Tirelle

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;
 
T

Tirelle

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
 
A

Allen Browne

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.
 
T

Tirelle

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"
 
T

Tirelle

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!!!
 
T

Tirelle

After I made these changes... My report performance went down drastically in
that it takes forever to open. Any sugestions?
 
T

Tirelle

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;
 
A

Allen Browne

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?
 
T

Tirelle

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
 
D

David W. Fenton

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.
 
D

David W. Fenton

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).
 
A

Allen Browne

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;
 
T

Tirelle

Thanks Allen. That Worked. The query is only slow when opening the report?
Why is that? Are there any alternatives?
 
D

David W. Fenton

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.
 
Top