Bhavbhuti Nathwani wrote:

>Hi Jonathan
>
>
>
>>I think this ought to work:
>>
>>
>
>Thanks a lot for you suggestion. I guess I can add another clause
><i>AND cMSF = 'R'</i> in the sub select to get the result.
>
>

Err, you're right, I forgot that in my subselect. Actually the SQL I
gave you is incorrect, it should be:

SELECT 1 AS iOrder, tDaily.*
FROM tDaily
WHERE cMSF = 'G' AND cDirPattern = '2+2' AND dDt = :vp_dAsOfDt
and (select last cMSF from tDaily where dDt < :vp_dAsOfDt and cDirPattern = '2+2' order by dDt desc) = 'R'
UNION ALL
SELECT 2 AS iOrder, tDaily.*
FROM tDaily
WHERE cMSF = 'G' AND cDirPattern = '2+1' AND dDt = :vp_dAsOfDt
and (select last cMSF from tDaily where dDt < :vp_dAsOfDt and cDirPattern = '2+1' order by dDt desc) = 'R'
UNION ALL
SELECT 3 AS iOrder, tDaily.*
FROM tDaily
WHERE cMSF = 'G' AND cDirPattern = '3+1' AND dDt = :vp_dAsOfDt
and (select last cMSF from tDaily where dDt < :vp_dAsOfDt and cDirPattern = '3+1' order by dDt desc) = 'R'
ORDER BY 1, 14 DESCENDING, 4, 5, 6

--

Best regards,
Jonathan Neve
_______________
CopyCat - advanced database replication
components for Delphi/C++Builder!
_______________________________________
Version 1.0 now available!
Web : http://www.microtec.fr/copycat



[Non-text portions of this message have been removed]