It was supposed to be an easy query!!!

[ResolutionsNET]

Hi,

Can anyone solve my problem, I have a table with the following data.

SessionID date time
12345676 010101 23:56
12345676 010101 23:57
12345676 010101 23:58
12345676 010101 23:59
12345676 020101 00:00
12345676 020101 00:01
12345676 020101 00:02
12345677 020101 00:05
12345677 020101 00:07
12345677 020101 00:10
12345677 020101 00:11
12345677 020101 00:34

what i need is a single query that will return the last record for a session. sessionid, date and time

e.g. for the above data all I need is
12345676 020101 00:02
12345677 020101 00:34

Anyone help

Thanks

 

[tlbroadbent]

Are date and time really two different columns on the table? That complicates things a little bit. Terry

http://members.home.net/tlbroadbent/index.htm

 

[foxdev]

If you use CAST along with some string-building and parsing with the Date and Time columns, it might work.

Something like:

[tt]select sessionid,
max(cast(date + ' ' + time as datetime))
from mytable group by sessionid [/tt]

except that the CAST expression will need to be a date format built by SUBSTRINGing out the appropriate pieces and inserting slashes. Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com

 

[tlbroadbent]

Now that I've had time to think about it, I think that what you want to do should actually be fairly easy - if the columns are separate character type columns.

select sessionid, max(date + ' ' + time) As NewDateTime
from mytable
group by sessionid

You will encounter a problem if the dates and times aren't all of the same format. For example, if AM times only have 7 characters (4:00:03) rather than 8. If the size is consistent this will work well.
Terry

http://members.home.net/tlbroadbent/index.htm

 

[ResolutionsNET]

Thanks guys, it worked perfectly.