[thelist] subselect query?

[Dallas Cahker]

I have a problem.  My problem is this.  I have a database of scans, the
scans are linked together by an invoice number, there might be 8-12 scans
per invoice, I need to get the invoice number of the scan that left area 20
on a certain date, after that I need to get the area it is currently in.
Can someone help me with getting the subselect correct for that, or is there
a better way to do it, even if it is 650,000 records.


SELECT B.RDSTAT, COUNT(*) FROM ORPRDETL A, ORPRDETL B WHERE A.RDSTAT = 200
AND B.\"RDORD#\" = A.\"RDORD#\" AND B.RDENDX = '2006-02-27' AND B.RDSTRX <>
'1940-01-01' AND B.RDSTAT = (SELECT MAX(C.RDSTAT) FROM ORPRDETL C WHERE
C.\"RDORD#\" = B.\"RDORD#\") GROUP BY B.RDSTAT - I dont get any invoice
numbers so I cant do any checks to see if the data is accurate.