How to find the latest row for each group of data
- by Jason
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
