title: How do I get the most recent database entry per ID, per day?
body: <p>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.</p>
  
  <p>Here is the query:</p>
  
  <pre><code>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 &gt;= 20140401 
              and x.Date &lt;= 20140403
              and x.City in ('LONDON', 'NEW_YORK', 'SHANGHAI', 'TOKYO')
              group by x.Id, x.City, x.Date
  </code></pre>
  
  <p>(NOTE: Id contains Location as part of its string)</p>
  
  <p>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?</p>