How do I get the most recent database entry per ID, per day?

Question

I am trying to extract the latest entry per Id, per day from a database table, in which the database is in Sybase SQL and the language of the report in which the query is written is Oracle SQL. I've tried modeling the query after SQL code posted elsewhere on this site, but it extracts other date and time values besides the max(). I suspect the reason is that some of the fields I'm using are obtained by conversion or string processing.

Here is the query:

Select
    substring(x.Id, 1, datalength(x.Id) - datalength(x.TimeAndAction)) as 'Identification',
    x.City,
    x.Date,
    max (Convert(Varchar(15), x.UTCTime, 108)) as 'MaxTime'
        from db_table x
            where x.Date >= 20140401 
            and x.Date <= 20140403
            and x.City in ('LONDON', 'NEW_YORK', 'SHANGHAI', 'TOKYO')
            group by x.Id, x.City, x.Date

(NOTE: Id contains Location as part of its string)

The problem is that less recent times (i.e. unwanted data) are also being included in the output of the query. How can I fix this?

Answer 1

The problem was that GROUP BY was based on Id, which was a field with each value in the database unique. The correct result appeared when I replaced

group by x.Id

with

group by substring(x.Id, 1, datalength(x.Id) - datalength(x.TimeAndAction))