SELECT idk FROM `mj_tmp` GROUP BY Concat(ida,u) HAVING Count(Concat(ida,u)) >1
First, you shouldn't use CONCAT() in your GROUP BY, as it breaks the possibility of using an index on ida or (ida,u) to speed things up. That would give you
SELECT idk FROM `mj_tmp` GROUP BY ida,u HAVING Count(*) >1
which is equivalent but probably faster. Now that the query is uncluttered by CONCAT(), perhaps you can see the problem. idk is not one of your GROUP BY columns. Many systems wouldn't allow this. MySQL does, but you are warned <http://dev.mysql.com/doc/mysql/en/GROUP-BY-hidden-fields.html> not to select columns whose values are not unique per group. If you do, you get essentially random data. (Actually, I think in the interest of speed you get the values in the first row found per group).
I'm guessing you simply want the oldest row for each ida,u group. Then the following should do what you want:
SELECT * FROM mj_tmp m1
WHERE d=(SELECT MAX(d) FROM mj_tmp m2
WHERE m1.ida=m2.ida AND m1.u=m2.u);This is one of three solutions to this problem documented in the manual <http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html>.
Now, I'm not sure why you are counting rows and using NOT IN. I also note that in your first post you had a date column d, but in this post you say it's F. In other words, I'm not certain I've really answered your question. Let us know if the above query doesn't meet your needs.
Michael
Mauricio Pellegrini wrote:
Thanks Jim,but that doesn't work for me because the order by performs after the records have been selected.
At that time I already have the unwanted row.
I will try to explain a little bit further
SELECT *
FROM `mj_tmp` This select is suposed to return all rows
WHERE idk NOT IN but only those whose idk is not found in the following result (idk is the primary key)
(SELECT idk FROM `mj_tmp` Here is where I need actually the youngest date GROUP BY Concat(ida,u) So the final result would deliver the oldest row HAVING Count( Concat(ida,u) ) >1 )
This last select should pick the youngest row to exclude them from the final result.
In other words I need a subselect whose rows selection is inverted taking column F into account (column F is a date)
On Mon, 2004-09-27 at 12:17, Jim Grill wrote:
Hi , I'm trying to fetch the oldest row from a result set in wich the resulting rows have duplicates,
I need only one row for each duplicate.
The problem is that I need always the oldest row Like in the example
SELECT *
FROM `mj_tmp` WHERE idk NOT IN (SELECT idk FROM `mj_tmp` GROUP BY Concat(ida,u) HAVING Count( Concat(ida,u) ) >1 )
I would like to get the first of this two rows but I'm always getting the second.
idk ida u d 90 14450 13 2004-09-08 147 14450 13 2004-09-09
Is there a way in which I can make sure I get always the oldest row?
Try adding something like ORDER BY d DESC LIMIT 1
Jim Grill
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
