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

1,A
5,B

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>)
GROUP BY 2

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

Edit:

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

Try:

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