468,539 Members | 1,543 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,539 developers. It's quick & easy.

returning N most recent records, iteratively

I need to perform the following:
- select the most recent X number of records in a table (there is a
timestamp field)
- select the Nth occurrence of X number of records

ex:
- most recent 10 records (N is 1)
- most recent records 10-20 (N would be 2)
- most recent records 10-30 (etc.)

What is the best method to do this?

Apr 25 '07 #1
2 3063
On 25 Apr 2007 13:58:38 -0700, "robert.waters"
<ro***********@gmail.comwrote:

For the 3rd one you probably meant: most recent records 20-30 (etc.)

SELECT TOP 10 *
FROM Orders
ORDER BY OrderDate;

SELECT TOP 10 *
FROM Orders
Where OrderID not in (SELECT TOP 10 OrderID FROM Orders ORDER BY
OrderDate)
ORDER BY OrderDate;

SELECT TOP 10 *
FROM Orders
Where OrderID not in (SELECT TOP 20 OrderID FROM Orders ORDER BY
OrderDate)
ORDER BY OrderDate;

-Tom.

>I need to perform the following:
- select the most recent X number of records in a table (there is a
timestamp field)
- select the Nth occurrence of X number of records

ex:
- most recent 10 records (N is 1)
- most recent records 10-20 (N would be 2)
- most recent records 10-30 (etc.)

What is the best method to do this?
Apr 26 '07 #2
Use a SELECT TOP query (sample below)

I'd be tempted to build the SQL statement in code using a form event.
The results could then be displayed in a datasheet-style subform by setting
the RecordSource of the subform to that manufactured SQL statement.
SELECT TOP 10 tblFldDate.flddate
FROM tblFldDate
WHERE (((Year([flddate]))=2007))
ORDER BY tblFldDate.flddate DESC;

Using the TOP predicate doe not return the highest values, nesecarily ...
without sorting the fldDate in descending sort order
(DESC) I get Jan1, 2, 3 ... With it I get Dec 31, 30, 29 ...

Dates/Times can be tricky to work with as well as they are just
"representations" (formats) of some really odd-looking numbers.
I'd be more inclined to use the SELECT TOP on an AutoNumber field to return
the TOP 'n' records (in the order they were entered) ... if that works for
you.
"robert.waters" <ro***********@gmail.comwrote in message
news:11*********************@r35g2000prh.googlegro ups.com...
>I need to perform the following:
- select the most recent X number of records in a table (there is a
timestamp field)
- select the Nth occurrence of X number of records

ex:
- most recent 10 records (N is 1)
- most recent records 10-20 (N would be 2)
- most recent records 10-30 (etc.)

What is the best method to do this?

Apr 26 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Nova's Taylor | last post: by
2 posts views Thread by news | last post: by
3 posts views Thread by manny | last post: by
13 posts views Thread by Kirk McDonald | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.