last complete entry

[permanentE]

I need some help.
My table daily_stats has these fields:
rowid, collectorid, entrytime, numcomplete

Each collectorid has an entry once a day at time entrytime, but sometimes the entry is not complete. What I'm trying to get:
For each collectorid, what is the rowid of the last entry that has numcomplete > 1440?

My incorrect solution (it is not valid SQL):
select rowid, max(entrytime) from daily_stats where numcomplete > 1440 group_by collectorid

-thanks

 

[BillDHS]

You can use a derived table to identify the collectorid and go back for the row ID:


SELECT RowID
FROM daily_stats,
(select CollectorID, max(entrytime) from daily_stats where numcomplete > 1440 group_by collectorid)DT(CID, MTime)

WHERE daily_stats.CollectorId = DT.CID
AND daily_stats.Entrytime = DT.Mtime;

 

[r937]

[tt]select rowid, collectorid, entrytime
from daily_stats ZZZ
where entrytime =
( select max(entrytime)
from daily_stats
where collectorid = ZZZ.collectorid
and numcomplete > 1440 )[/tt]

rudy

http://rudy.ca/

 

[permanentE]

Both of these answers work, but the BillDHS answer works twice as fast.

BillDHS, could you explain how that query works? Specially the DT(CID, MTime) part. I've never seen that before.

thanks

 

[BillDHS]

The method is referred to as a "Derived Table". What I am doing is defining a "work table" by placing a SELECT statement in the FROM clause.

The system will create and populate the work table and you can use it in the From and Where clause of the primary statement as if it were a physical table. DT is the table name and CID and Mtime are the columns in the same order as the select within the parenthesis.

The derived table will only exist as long as the query statement is running.