On 9 Sep 2012, at 5:19am, Kai Peters <kpet...@otaksoft.com> wrote:

> I do have an audit table with this structure:
> 
> AuditID
> ChangeDate
> RowID           (foreign/primary key in TableName)
> ActionType   
> TableName
> 
> and I want to query for records pertaining to a certain table name within a 
> given changedate range.
> I do, however, only want to receive the last (ChangeDate) record in cases 
> where more than one record 
> per rowid exist.

SELECT * FROM auditLines WHERE TableName = 'this' AND ChangeDate BETWEEN 
'20120810' AND '20120812' ORDER BY ChangeDate DESC LIMIT 1

Adjust the above for whatever format you're storing your dates in.
For fast searching add an index as follows

CREATE INDEX TCAuditLines ON (TableName,ChangeDate)

Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to