|
I have a
table my_table with the following columns Col_id Col_data1 Col_date Col_time I want to
retrieve the data for the most recent date and time for a specific ID The
following _expression_ works: SELECT col_data1,
col_date, col_time + INTO var_1
INDICATOR vind1, + vdate
INDICATOR vind2, + vtime INDICATOR
vind3+ FROM my_table
WHERE col_id = ‘test’ AND + col_date =
(select MAX(col_date) FROM my_table WHERE col_id = 'test') Except when
there are more that 2 records with different times for the same day it will
normally pick the first record (usually the one with the earliest time) The
following _expression_ will not work: SELECT col_data1,
col_data2, col_date, col_time + INTO var_1
INDICATOR vind1, + var_2
INDICATOR vind2 , + vdate
INDICATOR vind3, + vtime INDICATOR
vind4 + FROM my_table
WHERE col_id = ‘test’ AND + col_date =
(select MAX(DATETIME(col_date,col_time)) FROM my_table WHERE col_id = 'test') I know that
I can find the MAX date time first and the MAX time for that date and then
retrieve the data using these 2 values or I can create a view where I merge the
date and time columns, but I am trying to do it using one statement and no
views. TIA. Javier Valencia, PE President Valencia
Technology Group, L.L.C. 14315
S. Twilight Ln, Suite #14 Olathe,
Kansas 66062-4578 Office
(913)829-0888 Fax
(913)649-2904 Cell
(913)915-3137 ================================================ Attention: The information contained in this
message and or attachments is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. Any review,
retransmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited. If you received
this in error, please contact the sender and delete the material from all
system and destroy all copies. ====================================================== |
