find the date of xth from last order 1

[stevebanks]

hi!

I am trying to work out a way of finding out the details from the 1000th from last order from our order table.

Can someone help me with a query.., it's got me a little stumped!

Ta!

 

[lespaul]

is there a field that determines the order to decide what "last" is?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[stevebanks]

The order number is incremental if that's any help?

 

[PHV]

Something like this ?
SELECT * FROM yourOrderTable
WHERE OrderNumber=(SELECT Max(OrderNumber) FROM yourOrderTable)-1000

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[stevebanks]

I'm getting an error "invalid use of subquery" when i try this:

SELECT cms.odr_date
FROM cms
WHERE (((cms.order)=(SELECT Max(order) FROM cms)-1000));

The tables are coming from an ODBC link to a Foxpro DB which our mail order management system is built on... Would this cause the problem?

 

[PHV]

And this ?
SELECT odr_date
FROM cms
WHERE [order]=DMax('[order]','cms')-1000

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[stevebanks]

Thanks! A great help!