I've table with record data.

I am attempting to count the records arranged with a version, however , the records have another criteria (ref) and really should counted only one time (per ref).

stats sample data as one example of the problem:

id stat_date  ref version
1  2012-01-25   1  A
2  2012-01-25   2  B
3  2012-01-25   3  A
4  2012-01-26   8  B
5  2012-01-26   2  B
6  2012-01-26   3  B  <-- version has been updated for ref=3

Simple counting would return

SELECT COUNT(*),version FROM stat GROUP BY version


The issue here's that just the last record with ref=3 (id=6) ought to be counted and (id=3) needs to be overlooked. So now you ask ,, how do i filter row (id=3) in the query?

I can not evaluate which I ought to place as symptom in the subquery

SELECT COUNT(*),version FROM stats
WHERE  stat_date BETWEEN "2012-01-25"  AND "2012-01-26" 
AND id = (SELECT MAX(id) FROM stats WHERE <condition>)

The expected result could be:

1,A (since id=3 is ignored)
3,B (since the first id=2 is ignored and only id=5 is taken into account)

It does not matter which row you ignore. All that's necessary would be to count ref once.

SELECT COUNT(DISTINCT ref),version FROM stat GROUP BY version


Your example also needs to count the two rows where ref = 2 once too based on your logic.

This solutions satisfies the questions as posed even when it does not solve your unknown real problem


SELECT COUNT(*),version 
FROM stats s1
WHERE stat_date BETWEEN "2012-01-25"  AND "2012-01-26" AND
      not exists (SELECT null 
                  FROM stats s2 
                  WHERE s1.ref = s2.ref and s2.id>s1.id)
GROUP BY version

EDIT: Not certain to always work:

SELECT COUNT(*),version from
(select * from
 (select * from stats 
  where stat_date BETWEEN "2012-01-25"  AND "2012-01-26" 
  order by ref, id desc) s0
 group by ref) s1
group by version
SELECT COUNT(*),version FROM stats
   WHERE  stat_date BETWEEN "2012-01-25"  AND "2012-01-26" 
       AND id = (SELECT MAX(s.id) FROM stats s WHERE s.id = id)
   GROUP BY version
   ORDER BY stat_date