Return the top 10 only.

  • Thread starter Thread starter Michel Walsh
  • Start date Start date
M

Michel Walsh

You can rank in multiple ways, MS SQL Server 2005 has even an SQL
construction just for it. With Jet, using joins, that can be:


SELECT a.f1, a.f2, a.f3, COUNT(*) AS rank
FROM myTable As a INNER JOIN myTable AS b
ON a.fieldToRank >= b.fieldToRank
GROUP BY a.f1, a.f2, a.f3

HAVING COUNT(*) BETWEEN 250 AND 259


with the HAVING clause taking only rank-positions from 250 to 259,
inclusively.

If fieldToRank has duplicated values, some ranks will be duplicated too,
like: 1, 2, 3, 3, 3, 6, 7, 8, ...


Hoping it may help,
Vanderghast, Access MVP
 
Hi All,
I've made a make table query and I have column 'A' with expression
builder.It sort Descending.
The expresion calculate values from some field.I want to return
the top 10 highest number only.Ex : I have 300 record and the value
of column A is 1 untill 300.
I need everytime I run the query It will return record with value of
column'A'
from 300 untill 291 only.

It seems like a rank.What should I type in the criteria row ?

One again : how to create the automatic rank?
Like: 300 is 1st rank
299 is 2nd rank.

and so..on.

Would somebody help.
 
Thank's M.Walsh for the reply,
But I don't understand,cause you told me about sql.It trouble me.
I use access 2000,and just know a bit about acces.I'm trying to create
a simple program ,so,can you give me a simpler explanation.
 
To use the solution Michel gave you:

1. Create a new query.

2. Switch the query to SQL View (View menu, in query design.)

3. Paste in the query statement.

4. In the "FROM ..." line, replace the 2 instances of "myTable" with the
names of your tables.

5. Replace the F1, F2, etc with the names of your fields.

Access won't be able to show you the query graphically, but it will work
well.
 
Back
Top