> This table may contain many entries for any personId, but only the
> one with the most recent TransDate/TransTime is valid. The data
> actually comes from a foreign DB and TransNum is the numerical
> sequence within the data file.
Ben, I have to do this kind of stuff fairly often, and I always do it with a
separate view to get the maximums using a GROUP BY clause, then a
correlation to get the data from the original table. But your message
prompted me to look for the "right" way to do it. I used the Transmaster
table in Concomp, which is similar to your example. Unbelievably, the
following appears to work in R:Base:
SELECT * FROM TransMaster t1 +
WHERE TransDate = +
(SELECT MAX(TransDate) FROM TransMaster WHERE CustID = t1.CustID)
I don't recall seeing it documented that you can use this kind of singleton
select in place of a single value in a WHERE clause, but I have a vague
memory of one of the SQL-smart-guys on the list saying it does work. Even
then I wouldn't have guessed it would work in a correlated situation as
above, but it appears to. Cool!
(By the way, I just went to www.RSyntax.com and darn but it IS documented
there! There is one error in the docs, though, which say "When a SELECT
statement is part of a WHERE clause, it is called a sub-SELECT clause. A
sub-SELECT clause can contain only one column name (not a column list or *),
expression, or function. " In the example above, you can see that you _can_
use an expression. If you want to see the error, I suggest you check right
away, since as soon as Razzak sees this message the documentation will be
updated.)
--
Larry
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
