Top n results as a criterion

[j_Southern]

Years ago when I used Access I remember being able to limit the data downloaded from a database to the top 100 (or any other number) results of a chosen field.
I have searched my available help sources in vain for a way to do this with Excel. Is it possible? If so how.
John

[buran]

you should have some sort LIMIT clause in your SELECT statment

[romperstomper]

What exactly are you doing in Excel? If you are using an Autofilter there is a Top 10 option that allows you to choose however many records you want remaining.

[j_Southern]

I have a very simple query selecting three fields from the database. I want to limit the download to those records that show the maximum 100 values from one of the (numerical) fields.
I am looking for AutoFilter but can't as yet find it in Excel2007 .
John

[romperstomper]

If you're using MSQuery have you tried simply altering the SQL to a SELECT TOP 100 query?

[j_Southern]

romperstomper, Thanks for your interest. I have now found AutoFilter and the Top 10 option, and that will work, thank you.
However what I was looking for was the SQL solution. In Access I think this must have been automated. I do not know the syntax. Is it just SELECT TOP n ?
John

[romperstomper]

Yes - in conjunction with an ORDER BY clause so you get the right 'Top'.

[Speshul]

Please Login or Register  to view this content.

If you want all columns,

Please Login or Register  to view this content.

There is an option in Microsoft SQL Management studio, you can right click on any table and one of the options is to select top 1000 rows

st2k.PNG