How to find the latest row for each group of data

Question

Hi All,

I have a tricky problem that I'm trying to find the most effective method to solve.

Here's a simplified version of my View structure.

---

Table: Audits

AuditID | PublicationID | AuditEndDate | AuditStartDate
1       | 3             | 13/05/2010   | 01/01/2010
2       | 1             | 31/12/2009   | 01/10/2009
3       | 3             | 31/03/2010   | 01/01/2010
4       | 3             | 31/12/2009   | 01/10/2009
5       | 2             | 31/03/2010   | 01/01/2010
6       | 2             | 31/12/2009   | 01/10/2009
7       | 1             | 30/09/2009   | 01/01/2009 

There's 3 query's that I need from this. I need to one to get all the data. The next to get only the history data (that is, everything but exclude the latest data item by AuditEndDate) and then the last query is to obtain the latest data item (by AuditEndDate).

There's an added layer of complexity that I have a date restriction (This is on a per user/group basis) where certain user groups can only see between certain dates. You'll notice this in the where clause as AuditEndDate<=blah and AuditStartDate>=blah

Foreach publication, select all the data available.

select * from Audits
Where auditEndDate<='31/03/10' and AuditStartDate>='06/06/2009';

Foreach publication, select all the data but Exclude the latest data available (by AuditEndDate)

select * from Audits 
left join 
(select AuditId as aid, publicationID as pid
   and max(auditEndDate) as pend 
 from Audit where auditenddate <= '31/03/2009' /* user restrict */
 group by pid) Ax 
on Ax.pid=Audit.pubid
where pend!=Audits.auditenddate
AND auditEndDate<='31/03/10' 
and AuditStartDate>='06/06/2009' /* user restrict */

Foreach publication, select only the latest data available (by AuditEndDate)

select * from Audits 
left join 
  (select AuditId as aid, publicationID as pid
    and max(auditEndDate) as pend 
   from Audit where auditenddate <= '31/03/2009'/* user restrict */
   group by pid) Ax 
on Ax.pid=Audit.pubid
where pend=Audits.auditenddate
AND auditEndDate<='31/03/10' 
and AuditStartDate>='06/06/2009' /* user restrict */

So at the moment, query 1 and 3 work fine, but query 2 just returns all the data instead of the restriction.

Can anyone help me?

Thanks

jason

Answer 1

Okay, actually... Those queries work fine. It was my conditional PHP for running them. All good :)

Anyone who can suggest a more efficient method for this though I'd be interested in hearing that. I originally used sub queries in the where clause, and this ran really slow... from 2 seconds it went to 50 seconds....