[RBG7-L] - RE: SQL Question

[Javier Valencia]

Never mind, I answered my own question…   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 + DATETIME(col_date,col_time) = (select MAX(DATETIME(col_date,col_time)) FROM my_table WHERE col_id = 'test')   Is the correct answer… Javier,   P.S. Is it just my system or is Internet mail very slow today?     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. ======================================================   -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Javier Valencia Sent: Tuesday, July 20, 2004 12:59 PM To: RBG7-L Mailing List Subject: [RBG7-L] - SQL Question   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,   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. ======================================================