Not unbelievable, Larry - this is ANSI standard SQL. All the view does is make the
statement into one word for ease of typing when you use it, and ease of reading when
you look at it.
"Lawrence Lustig" <[EMAIL PROTECTED]> 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
>
>
__________________________________________________________________
Your favorite stores, helpful shopping tools and great gift ideas. Experience the
convenience of buying online with Shop@Netscape! http://shopnow.netscape.com/
Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/
