Is it possible to convert this query to use a join instead of a subquery?

Question

SELECT
    number, count(id)
FROM
    tracking
WHERE
    id IN (SELECT max(id) FROM tracking WHERE splitnr = 'a11' AND number >0 AND timestamp >= '2009-04-08 00:00:00' AND timestamp <= '2009-04-08 12:55:57' GROUP BY ident)
GROUP BY
    number

Answer 1

How about this:

SELECT    number, count(id)
FROM    tracking 
  INNER JOIN (SELECT max(id) ID FROM tracking  
                 WHERE splitnr = 'a11' AND 
                       number >0 AND timestamp >= '2009-04-08 00:00:00' AND 
                       timestamp <= '2009-04-08 12:55:57'
              GROUP BY ident
             ) MID ON (MID.ID=tracking.id)
WHERE   
GROUP BY number

Answer 2

Slightly hard to make sure that I've got it entirely right without seeing the data and knowing exactly what you're trying to achieve but personally I'd turn the sub-query into a view and then join on that, so:

create view vMaximumIDbyIdent

as

SELECT ident, max(id) maxid

FROM tracking

WHERE splitnr = 'a11' AND number >0

AND timestamp >= '2009-04-08 00:00:00'

AND timestamp <= '2009-04-08 12:55:57'

GROUP BY ident

then:

SELECT

number, count(id)

FROM

tracking,

vMaximumIDbyIdent

WHERE

tracking.id = vMaximumIDbyIdent.maxid

GROUP BY

number

More readable and maintainable.

Answer 3

Could you not do something like:

SELECT
    number,
    count(id)
FROM
    tracking
WHERE
    splitnr = 'a11' AND number > 0 AND timestamp >= '2009-04-08 00:00:00' AND timestamp <= '2009-04-08 12:55:57'
GROUP BY
    number
ORDER BY
    number DESC
LIMIT 0,1

(I don't really know MySQL by the way)

I'm assuming this would give you back the same resultset, you order it by the number desc because you want the maximum one, right? Then you can put the WHERE clause in and limit it by one to give you the first one which is essentially the same as MAX (I think) Thus removing the JOIN altogether.

EDIT: I didn't think you'd need the GROUP BY identd either