Re: selecting distinct records with group max value

[Tom Byars]

>> I agree it breaks the rules, but we just created the tmp table from a determined order, and we inserted no additional rows into it, so I'd be surprised if ORDER BY were needed selecting from it.

I'm not sure what you mean by this Peter. In the select part of the CREATE statement you have

SET @prev=-1;

SELECT
fld1, fld2, fld3, val,
IF( fld1 = @prev, 0, @prev := fld1 ) AS Sel
FROM tbl
ORDER BY fld1 ASC, val DESC;

If I was to run that code on its' own then I presume it would return every row in the Tbl table with the first row of each fld1 group (in the original order) having Sel>0 with the other rows having Sel=0. It would then order the rows by 'fld1 asc, val desc' such that the rows with Sel>0 could end up anywhere within their particular fld1 group and probably wouldn't have Val = group max(Val). Is that correct?

By appending the select statement to a CREATE it appears to me that the rows have been accessed in order 'fld1 ASC, Val DESC' so that the first row of each group (in sorted order) ends up with a Val equal to the group max(Val) and Sel>0 while the other rows in the group have Sel=0. i.e. It appears the
sort is done before any

IF( fld1 = @prev, 0, @prev := fld1 ) AS Sel

calculations are done. Is that correct and is that the norm?

If it is the norm then I cannot understand why my

SET @prev = -1;
CREATE TEMPORARY TABLE tmp
SELECT
fld1, fld2, fld3, val
WHERE IF( fld1 = @prev, 0, @prev := fld1)>0
FROM tbl
ORDER BY fld1 ASC, val DESC;

fails? You say it may have got 'munged' by WHERE clause optimisation. I can't say that gives me a lot of confidence in my struggle to come to terms with the workings of MySQL. It all seems a bit hit and miss to me. I'm also wondering as to how your example would work if you had Sel in the order by clause as it wouldn't be able to do the sort before executing the IF(... As Sel line. I'm becoming more confused.


>> It's the first query that's slow, right? Did you run EXPLAIN on it?

If you mean the query in my opening post then no. It only takes 0.5 seconds to run, it's the subsequent ones that take in the region of 50 secs because
they involve a select returning all rows. The original query doesn't guarantee finding the FIRST in the group with Val = group max(Val) though, unless the table's natural order = primary index order (the way the data is inserted I may be able to enforce this).


PS Fld1 is a DATE field. These DATE fields are a bit of a nippy sweety to work with as far as variable assignments are concerned.