Alan,
The example below works no problem so the logic is OK. You haven't got a
rogue index on date floating around have you?
Dave Crozier
***********
* Start
*Close Tables all
Set Date BRITISH
Create cursor curTrans (Document C(10), TransactionDate D, StockItem C(10),
Status C(1))
Insert Into curTrans Values ("Doc1", {^2000-01-01}, "A001", "1")
Insert Into curTrans Values ("Doc1", {^2000-01-01}, "A001", "1")
Insert Into curTrans Values ("Doc2", {^2000-01-02}, "A001", "2")
Insert Into curTrans Values ("Doc2", {^2000-01-01}, "A001", "4")
Insert Into curTrans Values ("Doc2", {^2000-01-04}, "A001", "5")
*
Insert Into curTrans Values ("Doc1", {^2000-02-01}, "A001", "1")
Insert Into curTrans Values ("Doc1", {^2000-01-01}, "A001", "1")
Insert Into curTrans Values ("Doc2", {^2000-02-02}, "A001", "2")
Insert Into curTrans Values ("Doc2", {^2000-01-01}, "A001", "4")
Insert Into curTrans Values ("Doc2", {^2000-02-04}, "A001", "5")
Insert Into curTrans Values ("Doc1", {^2000-02-01}, "B001", "1")
Insert Into curTrans Values ("Doc1", {^2000-02-01}, "B001", "2")
Insert Into curTrans Values ("Doc2", {^2000-03-01}, "B001", "3")
Insert Into curTrans Values ("Doc2", {^2000-02-01}, "B001", "1")
Insert Into curTrans Values ("Doc2", {^2000-02-01}, "B001", "5")
* Original Select
Select Top 3 Distinct ;
TransactionDate ;
>From curTrans ;
Where StockItem="A001" ;
Order By TransactionDate desc
* Combined Select
Select * ;
>From curTrans ;
Where StockItem="A001" ;
And TransactionDate in ;
(Select Top 3 Distinct ;
TransactionDate ;
>From curTrans ;
Where StockItem="A001" ;
Order By TransactionDate Desc) ;
Order By curTrans.TransactionDate desc
*
* END
******
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of Alan Bourke
Sent: 03 July 2008 11:37
To: [email protected]
Subject: Subquery oddity.
I have a transactions file, and I want to get the records for three most
recent transactions. Say the file is of the form:
Document, Transaction Date, Stock Item, Status
I could have multiple records with the same Document, Date and Stock
Item but with a different Status.
So to get the 3 most recent dates that a particular stock item was
involved in, I use this:
Select Top 3 distinct TransactionDate From TransactionFile Where
StockItem = "X-250358" Order By TransactionDate Descending
This returns 3 dates.
So then I incorporate that as a subquery thus:
Select * from TransactionFile where StockItem = "X-250358" And
TransactionDate in ( <query above that returned 3 records> )
However this only returns records for two of the three dates that the
original query returned. The records for the earliest date are missing.
Any idea why ?
--
Alan Bourke
[EMAIL PROTECTED]
[excessive quoting removed by server]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
