none
SQL top 10 returns more than 10 records RRS feed

  • Question

  • Please assist in understanding why this SQL returns more than the top ten results. I am getting 22 records returned out of 900.

    SELECT TOP 10 Assets.[Asset ], Assets.[Serial #], Assets.User, Assets.Location, Assets.[Installation Date], Assets.[Active Date], Assets.[Asset Description], Assets.Category
    FROM Assets
    WHERE (((Assets.[Asset Description]) Not In ("APC Smart UPS 3000","SUA 3000 RMT2U")) AND ((Assets.Category)="UPS"))
    ORDER BY Assets.[Active Date];
    

    Thank you!!!


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Thursday, January 10, 2019 10:42 PM

Answers

  • If there are multiple records with the value in 10th place, ALL of them will be returned by a Top 10 query.

    A simple example:

    SomeField
    1
    1
    1
    1
    2
    2
    2
    3
    3
    4
    4
    4
    4
    4
    4
    4
    5
    5
    6

    Here, the value in 10th place, i.e. 4, occurs 6 times. All 6 records are included in a Top 10 query, so it returns 15 records:

    SomeField
    1
    1
    1
    1
    2
    2
    2
    3
    3
    4
    4
    4
    4
    4
    4
    4


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by KCDW Friday, January 11, 2019 5:00 PM
    Thursday, January 10, 2019 10:51 PM
  • Your query is sorted on Active Date:

    ORDER BY Assets.[Active Date];

    So the Top 10 is evaluated on the Active Date field.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by KCDW Friday, January 11, 2019 5:00 PM
    Thursday, January 10, 2019 11:02 PM
  • If you want to return exactly 10 rows, discarding any rows which tie on ActiveDate with the 10th row returned, then one solution would be to sequentially number the rows in ActiveDate order in a query, and restrict the result set to those rows where the row number is <=10.

    You'll find examples of queries which number rows in RowNumbering.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    The most efficient method is to join two instances of the table, which the first example in my demo does.  So if my query is amended by the addition of a HAVING clause only the first 10 rows in the result set will be returned:

    SELECT COUNT(*) AS RowNumber, T1.TransactionDate, T1.CustomerID, T1.TransactionAmount
    FROM Transactions AS T1 INNER JOIN Transactions AS T2
    ON (T2.TransactionID<=T1.TransactionID OR T2.TransactionDate<>T1.TransactionDate)
     AND (T2.TransactionDate<=T1.TransactionDate)
    GROUP BY T1.TransactionDate, T1.TransactionID, T1.TransactionAmount, T1.CustomerID
    HAVING COUNT(*) <=10;

    Note how the primary key TransactionID is used as the tie breaker in the event of there being two or more transactions on the same date.  In your case you would also have to restrict both instances of the table on the Asset Description and Category columns in a WHERE clause preceding the GROUP BY clause.

    Ken Sheridan, Stafford, England

    • Marked as answer by KCDW Friday, January 11, 2019 5:00 PM
    Friday, January 11, 2019 12:04 PM

All replies

  • If there are multiple records with the value in 10th place, ALL of them will be returned by a Top 10 query.

    A simple example:

    SomeField
    1
    1
    1
    1
    2
    2
    2
    3
    3
    4
    4
    4
    4
    4
    4
    4
    5
    5
    6

    Here, the value in 10th place, i.e. 4, occurs 6 times. All 6 records are included in a Top 10 query, so it returns 15 records:

    SomeField
    1
    1
    1
    1
    2
    2
    2
    3
    3
    4
    4
    4
    4
    4
    4
    4


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by KCDW Friday, January 11, 2019 5:00 PM
    Thursday, January 10, 2019 10:51 PM
  • Thanks Hans.

    So the Asset Number has no Duplicates and is the PK. Shouldn't that mean there are no repeating numbers?


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Thursday, January 10, 2019 10:56 PM
  • Your query is sorted on Active Date:

    ORDER BY Assets.[Active Date];

    So the Top 10 is evaluated on the Active Date field.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by KCDW Friday, January 11, 2019 5:00 PM
    Thursday, January 10, 2019 11:02 PM
  • If you want to return exactly 10 rows, discarding any rows which tie on ActiveDate with the 10th row returned, then one solution would be to sequentially number the rows in ActiveDate order in a query, and restrict the result set to those rows where the row number is <=10.

    You'll find examples of queries which number rows in RowNumbering.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    The most efficient method is to join two instances of the table, which the first example in my demo does.  So if my query is amended by the addition of a HAVING clause only the first 10 rows in the result set will be returned:

    SELECT COUNT(*) AS RowNumber, T1.TransactionDate, T1.CustomerID, T1.TransactionAmount
    FROM Transactions AS T1 INNER JOIN Transactions AS T2
    ON (T2.TransactionID<=T1.TransactionID OR T2.TransactionDate<>T1.TransactionDate)
     AND (T2.TransactionDate<=T1.TransactionDate)
    GROUP BY T1.TransactionDate, T1.TransactionID, T1.TransactionAmount, T1.CustomerID
    HAVING COUNT(*) <=10;

    Note how the primary key TransactionID is used as the tie breaker in the event of there being two or more transactions on the same date.  In your case you would also have to restrict both instances of the table on the Asset Description and Category columns in a WHERE clause preceding the GROUP BY clause.

    Ken Sheridan, Stafford, England

    • Marked as answer by KCDW Friday, January 11, 2019 5:00 PM
    Friday, January 11, 2019 12:04 PM