>Dmitry,
>
>I follow such questions / discussions, because there are corners of SQL I am uncertain about or just ignorant. That said, In your shoes I'd
never put the smart 1 query code in any of my apps
>
>The basic OP question was clear and defined 2 selection steps. Why not KISS and code in 3 queries ? That way, you can be certain that you will not run afoul implementation differences or updates on esoteric SQL and if somebody else (or a future Dmitry...) touches that code it will still resolve to the result specced.
>in Vfp there would be no need for even result cursor, better here as the JOIN is a filter
>SELECT MAX(DATE_FLD) As mD FROM MyTable into Array aT_Date
>SELECT MAX(ORDER_NO) AS mO FROM MyTable where DATE_FLD = aT_Date[1] into Array aT_Ord
>SELECT fields_needed_list FROM MyTable;
> where DATE_FLD = aT_Date[1] and ORDER_NO = aT_Ord[1]
>
>There is no need for checking _tally if there is at least 1 record, as filter is a self join.
>So yes, ask questions- get smarter - but keep your code free of the answers ;-)
>
>my 0.02€
>thomas
Thomas,
It appears to me that the 3 Selects would not work for me (at least in the form that you described). The first SELECT does not take into account several WHERE expressions (e.g. WHERE DATE is within a range). Otherwise, the first SELECT is useless since it will select MAX(DATE_FLD) As mD from the entire table.
But, if the WHERE clauses were to be added to the SELECT(s) where they need to be, the 3 SELECT(s) would work.
However, I like Tamar's approach (with one SQL Select and 2 JOINS).
Thank you.
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham