Re: [sqlite] Can this be done in one query?

[Darren Duncan]

Petite, well yes, the syntax you gave, subquery in the from clause, is 
functionally equivalent to the one I gave; either way we are returning the foo 
records that match the result of the subquery.  In other words, I agree with 
you, and could have written it the way you did, but I considered my choice less 
verbose; in practice, one would more likely do it how you did, however. -- 
Darren Duncan


Petite Abeille wrote:

On Sep 9, 2012, at 6:51 AM, Darren Duncan  wrote:


You will need to use a subquery to do what you want, because you want to do a 
join on the results of a group by.  This is one example of syntax:

 select * from audtbl where (RowID, ChangeDate) in
   (select RowID, max(ChangeDate) as ChangeDate from audtbl group by RowID);


As far as I know, such syntax (match on multiple 'in' columns) is not supported 
by SQLite.

However this can easily be turned into a simple self-join:

select  * 
fromfoo 
join(

  selectid,
max( date ) as date
  from  foo

  group by  id
)
as  bar
on  bar.id = foo.id
and bar.date = foo.date

As mentioned by Igor, there was a recent thread about this exact topic with 
various other approaches:

http://thread.gmane.org/gmane.comp.db.sqlite.general/76558


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

Re: [sqlite] Can this be done in one query?

[Petite Abeille]

On Sep 9, 2012, at 6:51 AM, Darren Duncan  wrote:

> You will need to use a subquery to do what you want, because you want to do a 
> join on the results of a group by.  This is one example of syntax:
> 
>  select * from audtbl where (RowID, ChangeDate) in
>(select RowID, max(ChangeDate) as ChangeDate from audtbl group by RowID);

As far as I know, such syntax (match on multiple 'in' columns) is not supported 
by SQLite.

However this can easily be turned into a simple self-join:

select  * 
fromfoo 
join(
  selectid,
max( date ) as date
  from  foo

  group by  id
)
as  bar
on  bar.id = foo.id
and bar.date = foo.date

As mentioned by Igor, there was a recent thread about this exact topic with 
various other approaches:

http://thread.gmane.org/gmane.comp.db.sqlite.general/76558

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

Re: [sqlite] Can this be done in one query?

[Darren Duncan]

Kai Peters 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.


Any help appreciated,


You will need to use a subquery to do what you want, because you want to do a 
join on the results of a group by.  This is one example of syntax:


  select * from audtbl where (RowID, ChangeDate) in
(select RowID, max(ChangeDate) as ChangeDate from audtbl group by RowID);

-- Darren Duncan

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

Re: [sqlite] Can this be done in one query?

[Igor Tandetnik]

Kai Peters  wrote:
> 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.

A very similar problem was discussed extensively just the other day. See this 
thread:

http://www.mail-archive.com/sqlite-users@sqlite.org/msg72319.html

-- 
Igor Tandetnik

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

Re: [sqlite] Can this be done in one query?

[Simon Slavin]

On 9 Sep 2012, at 5:19am, Kai Peters  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