Larry,
I got to looking at this some more. So many transactions had the
same date that the select filtered out very few records. So I used
the datetime function and it got better. But darned if there weren't
a bunch of records with duplicate Ids/dates/times (I had thought
that would be a rare occurrence). I ended up deleting the duplicates
and using your select construct. Works like a champ; I would never
have thought of using that correlation.
Thanks,
Ben Petersen
On 17 Aug 2001, at 19:16, Lawrence Lustig wrote:
> > 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
>
>
