Thread: How to extract record w/ latest time?

Last post 04-15-2008 3:18 PM by cbv. 2 replies.

Average Rating Rate It (5)Thank you for the rating!

RSS

Page 1 of 1 (3 items)

Sort Posts:

04-10-2008, 10:53 PM

cbv
Joined on 03-21-2008, 8:45 PM
Posts 3

How to extract record w/ latest time?

Reply Contact

Running this:

"select distinct FILE_NAME, STRCAT ( STRCAT ( EXTRACT_TOKEN( LAST_EDIT, 2, ' ' ), ' '), EXTRACT_TOKEN( LAST_EDIT, 4, ' ' )) AS Time from Records.txt ORDER BY FILE_NAME" -i:TSV

... returns:

FILE_NAME    TIME
File1    LAST SAVE: 04/10/08 @ 13:19:15 BY: TM PLOT SC 1/4"=1'
File1    LAST SAVE: 04/10/08 @ 13:35:57 BY: TM PLOT SC 1/4"=1'
File1    LAST SAVE: 04/10/08 @ 13:51:19 BY: TM PLOT SC 1/4"=1'
File2    LAST SAVE: 04/10/08 @ 13:51:38 BY: TM PLOT SC 1/4"=1'
File2    LAST SAVE: 04/10/08 @ 14:09:56 BY: TM PLOT SC 1/4"=1'
File3    LAST SAVE: 04/10/08 @ 14:05:00 BY: TM PLOT SC 1/4"=1'
File4    LAST SAVE: 04/10/08 @ 14:09:07 BY: TM PLOT SC 1/4"=1'
File5    LAST SAVE: 04/10/08 @ 14:53:23 BY: TM PLOT SC 1/4"=1'
File5    LAST SAVE: 04/10/08 @ 14:58:10 BY: TM PLOT SC 1/4"=1'
File5    LAST SAVE: 04/10/08 @ 15:16:30 BY: TM PLOT SC 1/4"=1'
File6    LAST SAVE: 04/10/08 @ 15:00:07 BY: TM PLOT SC 1/4"=1'
File6    LAST SAVE: 04/10/08 @ 15:27:02 BY: TM PLOT SC 1/4"=1'

How do I extract only records having the latest date / time?

Thank You,

Chris

04-15-2008, 3:38 AM In reply to

Zhao Ji Ma - MSFT
Joined on 01-05-2007, 5:36 AM
Posts 765

Re: How to extract record w/ latest time?

Reply Contact

Hi Chris,

You can use this sample code:

select distinct file_name, to_timestamp(date, 'MM/dd/yy') as value from FILE where value in (select min(to_timestamp(date, 'MM/dd/yy')) from FILE)

Zhao Ji Ma
Sincerely,
Microsoft Online Community Support

“Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. ”

04-15-2008, 3:18 PM In reply to

cbv
Joined on 03-21-2008, 8:45 PM
Posts 3

Re: How to extract record w/ latest time?

Reply Contact

Zhao Ji Ma,

Thank You for your reply. I have tried it and it works. I was also searching the forum for help and came across http://forums.iis.net/p/1144790/1852237.aspx#1852237. Using it as a guide, here is the solution I made. The EXTRACT_TOKEN functions are used because the date and time strings were embedded in the LAST_EDIT field:

logparser
"select
    distinct
    FILE_NAME, DateTime
    USING
      TO_TIMESTAMP
        (TO_TIMESTAMP (EXTRACT_TOKEN( LAST_EDIT, 2, ' ' ), 'MM/dd/yy'),
         TO_TIMESTAMP (EXTRACT_TOKEN( LAST_EDIT, 4, ' ' ), 'hh:mm:ss')
        )
        AS DateTime
    FROM filename
WHERE (FILE_NAME, DateTime) IN
(select
    FILE_NAME, Max(DateTime)
    USING
      TO_TIMESTAMP
        (TO_TIMESTAMP (EXTRACT_TOKEN( LAST_EDIT, 2, ' ' ), 'MM/dd/yy'),
         TO_TIMESTAMP (EXTRACT_TOKEN( LAST_EDIT, 4, ' ' ), 'hh:mm:ss')
        )
        AS DateTime
    FROM filename GROUP BY FILE_NAME)
ORDER BY FILE_NAME"
-i:TSV

Thank You again!

Tags: latest timestamp TO_TIMESTAMP

Page 1 of 1 (3 items)