Select Top X not working

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

Answer 1

User-1971614856 posted

I think TOP is not valid in MS Access

Answer 2

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.

 

 

Answer 3

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

Answer 4

User1283497924 posted

Hi,

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

Answer 5

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

Answer 6

User-1153490049 posted

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

 

Answer 7

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

Answer 8

User275991403 posted

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

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

-Saravanan

Answer 9

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.