Re: selecting distinct records with group max value

[Tom Byars]

Peter, I'm confused here. Are the rows in your last example accessed in the 'order by' order? I was aware that if you done an update query on a table and the query had an 'order by' clause then the rows would be updated in 'order by' order but, I thought your example wouldn't work because the order by wouldn't be performed until after all the rows were fetched. I assume I was wrong as your example does work.

I say it works but, shouldn't the order by clause read
order by fld1, val desc, fld2, fld3
to ensure it sets sel>0 for the FIRST row (with respect to Primary index) in the group with val=max(val)?

And why doesn't this yield the same table minus the Sel=0 rows?

DROP TEMPORARY TABLE IF EXISTS tmp;
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;



Regardless of that it's as slow as the example I gave you in my last post since it involves having to insert the whole table. I guess whatever solution I use it's going to be slow because of the ordering so I won't ask you to waste any more time on it aside,hopefully, from answering the above questions.