Need to select first N rows which meet criteria

[Guest]

Table is sorted by column "SORTID", "STORE" IS A FIELD NAME
Table is emptied, then "INSERT INTO"'d a query with ORDER BY

Need to "select top N store" rows of this table in the order it's currently in
Currently it arbitrarily selects top N stores and when you use "TOP N" you can't order by

If it was only one row, I'd use FIRST(STORE), but I need N rows.
Only solution I can think of is to loop a FIRST(STORE) Query followed by a Delete Query N times

Is there a better way

Thanks

Mik

 

[Brian Camire]

See comments embedded below.

Table is sorted by column "SORTID", "STORE" IS A FIELD NAME.

Tables are not sorted. If you want to see a sorted "view" of what's in a
table, you can use a query.

Table is emptied, then "INSERT INTO"'d a query with ORDER BY.

When you INSERT TO, the data ends up a table, not a query. Data is stored
in tables. Queries are just "views" of that data. Since tables are not
sorted, using ORDER BY to INSERT INTO has no effect.

Need to "select top N store" rows of this table in the order it's currently in.
Currently it arbitrarily selects top N stores and when you use "TOP N" you

can't order by.

When you use TOP, you must ORDER BY to get non-arbitrary results.

If it was only one row, I'd use FIRST(STORE), but I need N rows.

The result returned by FIRST is effectively arbitrary, unless the values
you're applying it to are all the same.

Only solution I can think of is to loop a FIRST(STORE) Query followed by a Delete Query N times.

Is there a better way ?

Yes.

If N is fixed (like, say 10), you migh use a query whose SQL looks something
like this:

SELECT TOP 10
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].SORTID