locked
Select Top X not working RRS feed

  • Question

  • User253847291 posted

    hello,

    ive a problem with ms access query; im trying to solect top X records and it fails returning > X each time. without the TOP keyword it would filter a lot more records, with TOP keyword it alwasy select more than required; im selecting 4 fields and performing value check on 3 of them, ive noticed that if i limit to different values of TOP it works in some cases while doesnt in others; it seems that it select TOP X records with specific value in the last field grouping the results until it select X records; here is an exaple:

    id     vi_se  ri_no  coeff
    af418    16    27    0.592592592592593
    de788    18    31    0.580645161290323
    al452    22    38    0.578947368421053
    du771    14    25    0.56
    ns011    15    27    0.555555555555556
    iv729    15    27    0.555555555555556
    ge103    15    27    0.555555555555556

    in this case the effective records are 7 however if you group by coeff they are 5 as requested by the TOP keyword, this is the exact query result, there is no grouping performed on coeff field, in addition in this case the last three values are identical. I also used DISTINCT keyword with no results.


    <meta http-equiv="Content-Type" content="text/html;charset=UTF-8"> <title>qryRilevanzaFissa</title> <tfoot></tfoot>
    qryRilevanzaFissa
    ambo volte ritardo ril
    16,50 16 27 0.592592592592593
    26,62 18 31 0.580645161290323
    43,62 22 38 0.578947368421053
    11,20 14 25 0.56
    2,9 15 27 0.555555555555556
    2,16 15 27 0.555555555555556
    9,50 15 27 0.555555555555556
    Tuesday, August 31, 2010 3:15 AM

Answers

  • User1283497924 posted

    Hi,

    plz use the below query this will return top 5 values...

    from ur table

    SELECT top 5  [a_id] & [n_id] AS id, [vi_se], [ri_no],  [vi_se] / [ri_no] AS coeff from TableName ORDER BY [vi_se] / [ri_no] DESC

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 1, 2010 5:58 AM

All replies

  • User-1971614856 posted

    I think TOP is not valid in MS Access

    Tuesday, August 31, 2010 3:27 AM
  • User-1153490049 posted

    " select top 10 * from tablename order by ColumnX "

    Suppose ColumnX has repeat records, then you will get more than 10 rows. 

    Suppose ColumnX does not have repeat records, then you will get less than 10 rows.

    So please use the primary key as order by column, e.g: select top 10 * from tablename order by AutoInc, autoinc is auto generated.

     

     

    Tuesday, August 31, 2010 3:34 AM
  • User253847291 posted

    So please use the primary key as order by column, e.g: select top 10 * from tablename order by AutoInc, autoinc is auto generated.

    hello,

    but i need the first 10 rows ordered from coeff value not a different sort, wihtout TOP it pulls over 72 rows and i want the first 10 ordered by coeff

    Tuesday, August 31, 2010 1:28 PM
  • User1283497924 posted

    Hi,

    can provide the query which u r using for accessing the data...

    Tuesday, August 31, 2010 1:50 PM
  • User253847291 posted

    can provide the query which u r using for accessing the data...

    SELECT [a_id] & [n_id] AS id, [vi_se], [ri_no],  [vi_se] / [ri_no] AS coeff ORDER BY [vi_se] / [ri_no] DESC

    Tuesday, August 31, 2010 1:57 PM
  • User-1153490049 posted

    select top 10 *  from tableName order by  [vi_se] / [ri_no], YourPrimaryKey DESC

     

    Tuesday, August 31, 2010 9:14 PM
  • User1283497924 posted

    Hi,

    plz use the below query this will return top 5 values...

    from ur table

    SELECT top 5  [a_id] & [n_id] AS id, [vi_se], [ri_no],  [vi_se] / [ri_no] AS coeff from TableName ORDER BY [vi_se] / [ri_no] DESC

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 1, 2010 5:58 AM
  • User275991403 posted

    Hi, View this below link.I hope it will help you

    http://www.fmsinc.com/tpapers/queries/



    -Saravanan

    Wednesday, September 1, 2010 6:40 AM
  • User1491597376 posted

    SELECT [a_id] & [n_id] AS id, [vi_se], [ri_no],  [vi_se] / [ri_no] AS coeff ORDER BY [vi_se] / [ri_no] DESC


    I cant see table name in your query..............correct me if I am wrong coz m just unable to find it.


    Wednesday, September 1, 2010 7:15 AM