Return 20 Rows from Query

D

David

Hello,

Any idea how to return 20 rows from a query? I don't want to use ranking
(Top 20) as that doesn't properly address duplicate values.

Problem: I have a set of records with a score. I just need the top 20
records. Using "TOP 20" includes duplicates, and therefore, I get > 20
records returned.

Thanks.
 
J

John Spencer

Since TOP n is determined by the sort order and returns any "ties" in the
sort order, add the primary key field(s) to the sort order. Then there
cannot be any ties and you will get up to the number of records you
requested. UP TO meaning that if there are only 15 records in the table,
you can only get 15 back even if you request 20.

If you don't have primary keys in your table(s) then you should add them.
If you can't then make sure you add enough fields to the sort order to force
unique sorting order.
 
D

David

Thanks.

I guess I'll do a "pre-query" to use my AUTOID field, sorted, then a second
query to grab those 20 records delivered in the first query.

Cheers!
 
D

David

John,

When I try that, it doesn't return 20 rows.

I need to sort on my AUTOID field for this to work. The problem with that
is that I want to use a SCORING field, sorted in Descending Order, to decide
which rows to return.

If I don't sort on AUTOID, I get 46 rows.

If I do sort on AUTOID, I get 20 rows, but not the Top 20 scored rows.
 
D

David

I should also note: The "SCORE" field I refer to is a COUNT calculated field
in this SUM/GROUPING query. So I am counting instances of records grouped
together, and wanting to sort on that count, then grab the top 20 of those
records.
 
T

Tom Ellison

Dear David:

John's instructions were to sort by BOTH the SCORE and the AUTOID, in that
order. You'll get the top scores, but one of more that "tie" for 20th place
will be dropped.

Tom Ellison
 
D

David

Thanks.
--
David


Tom Ellison said:
Dear David:

John's instructions were to sort by BOTH the SCORE and the AUTOID, in that
order. You'll get the top scores, but one of more that "tie" for 20th place
will be dropped.

Tom Ellison
Click to expand...
 
J

John Spencer

Try Sort by both fields at once. You don't need a second query.

SORT BY SCORING DESC, AutoID

IN SQL your query would look like

SELECT TOP 20 Scoring, SomeOtherField
FROM YourTable
ORDER BY Scoring Desc, AutoID

Not that you don't have to display AutoId, you only have to sort by it.
 
J

John Spencer

David,
You may need to post the SQL text of your query. And some suggestions as to
which fields can be used to determine a unique set of values in the order by
clause.
 
Top