Selecting 'middle' records

[orlandoj]

Hi,

I know how to make a query to select the top 100 rows of a table:

SELECT TOP 100 * FROM ... ORDER BY ...;

How would I make a query that selected rows 100-200. A pseudocode example would be:

SELECT MIDDLE 100-200 FROM ... ORDER BY ...;

Is there a way to do this?

Thanks,
-Jamie

 

[mpastore]

I guess I would try...

select top 100 from mytb where my key not in
(select top 100 mykey from mytb .. order by)
order by ...

Hats off to Harper

 

[orlandoj]

Hmmmm .... seems rather inefficient in design. That query has to check every row to see if it is NOT in the top 100 rows, and then get the top 100 rows from that result set. Is there a more efficient way to just return records 100-200?

Thanks,
-Jamie

 

[mpastore]

Good point, maybe create a temp table with the top 200 records then doing the inefficient one on the temp table?



Mike Pastore

Hats off to (Roy) Harper

 

[SgtJarrow]

mpastore is on the right track......

I would open a recordset to the orignal table, jump to record 100, loop through the next 100 records adding them to a temp table, and then use this table...shoudl only take a fraction of asecond to created the temp table, could be set up to start at any number and get any number you want, and is fully controllable....

Let me know if you need any help with the code....

****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com

 

[mpastore]

Sometimes temp tables are the most efficient for the database engine, I've found this whether it's Oracle or our humble Jet engine. Issue is database bloat for the front-end .mdb. If you are running the temp table process repeatedly look into an occasional compact routine.

Mike Pastore

Hats off to (Roy) Harper

 

[SgtJarrow]

I find that when I know a temp table may cause bloating (which is almost always), I create a temporary database to put my temporary tables into. My code will create the database, create the temp tables, do the work, then just delete the datasbase.....keeps your permanent database small, doesn't decrease lag much, and everything goes on its merry way.

****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com

 

[mpastore]

That's a good idea, need to look into doing that for an app I've been working on. Thanks

Mike Pastore

Hats off to (Roy) Harper

 

[orlandoj]

What is the code to jump to a specific record in a recordset?

Thanks,
-Jamie

<<I would open a recordset to the orignal table, jump to record 100.....>>

 

[mpastore]

Not sure there is code like the recno() equivalent you would find in dbase/Foxpro.

You could just open the recordset and scan 100 records...

dim rs as recordset
set rs = currentdb.openrecordset(&quot;tmpTable&quot

dim iRecCntr as integer

rs.movefirst
iRecCntr=1
do while not rs.eof and not iRecCntr>100
rs.movenext
iRecCntr=iRecCntr+1
loop


*** you will need to test this code, I have not tested it


Mike Pastore

Hats off to (Roy) Harper

 

[SgtJarrow]

You can also move using the .move command....it allows you to specify the number of records to move...

rs.move (100)

will move 100 records from the current record

****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com

 

[mpastore]

1768

Thanks, didn't even know that method existed.

Mike Pastore

Hats off to (Roy) Harper

 

[raskew]

This appears to be a verbose thread which is long on talk and short on facts.

Could we throw aside all the mutual backslapping and get down to facts.

Scenario: Suppose we need a recordset that consists of the 41st thru 80th record in Northwind’s Orders table, predicated on the [Freight] field, sorted Descending.

Can anyone offer a workable, on-the-fly, solution (complete with code/query-sql), particularly one where 41-80 could well change to 31-50, or some other variation?

Best wishes,

Bob

 

[mpastore]

I'll return to my original solution. This will select 2nd set of 10 records. Inefficient or not, it will do it.

SELECT TOP 10 Orders.freight
FROM Orders
WHERE orderID not in(select top 10 OrderID from Orders order by freight desc)
ORDER BY Orders.freight DESC;

With VBA code I would just select whole record set in desc/asc order and use the rs.move() to get a slice of records.

raskew, I'm sure you can make this all &quot;on the fly&quot; via some kind of generic routine. I promise not to slap your back too hard if you do.


Mike Pastore

Hats off to (Roy) Harper

 

[scriverb]

In the event that this table is very very large as we all know the subquery comparison becomes bogged down. If the file is small that is the way to go. But, here is an alternative where you modify three saved queries with range parameters and then just run the final query to get the results. Create 3 saved queries titled qryTOP_Pass1, qryTOP_Pass2, and qryTOP_Final. Just select table orders and select a single field. It doesn't matter what is in the query just save them by the names indicated. The following code demonstrates the technique and could be setup in a Subroutine with parameters to pass the upper and lower limits.

Dim db As DAO.Database
Dim vUpper As Long
Dim vLower As Long
Set db = CurrentDb
vUpper = 100
vLower = 200
'This query selects records down through the lower limit
db.QueryDefs(&quot;qryTOP_Pass1&quot.SQL = &quot;SELECT TOP &quot; & vLower & _
&quot; A.freight FROM Orders as A ORDER BY A.freight DESC;&quot;
'This query reverses the sort of the initial select and selects the TOP records needed
db.QueryDefs(&quot;qryTOP_Pass2&quot.SQL = &quot;SELECT TOP &quot; & (vLower - vUpper) & _
&quot; A.freight FROM qryTOP_Pass1 as A ORDER BY A.freight;&quot;
'This query takes the final recordset and puts it back into the requested order.
db.QueryDefs(&quot;qryTOP_Final&quot.SQL = &quot;Select * FROM qryTOP_Pass2 &quot; & _
&quot;ORDER BY Order.freight;&quot;
DoCmd.OpenQuery &quot;qryTOP_Final&quot;

By executing the last query you select the middle records selected from the original table.



Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???