Find the id of the max-value

[sirugo]

I can't be the first one with this problem:

I simply want to find the row-ids that hold the MAX-values grouped by specific column names.

Instinctively I would use:

SELECT ix, column1, MAX(column1)
FROM table
GROUP BY column2, column3

but this does not even give the same values of column1 and MAX(column1)
I also tried variations on subqueries and aliases for the table but no luck.

 

[sirugo]

OK, I finally solved it using a subselect.
It went wrong on the final WHERE, but now it works

SELECT this, that
FROM table t1
WHERE column1 =
(
SELECT MAX(t2. column1)
FROM table t2
WHERE t1. column2 = t2.column2 AND t1.column3 = t2. column3
)

 

[sirugo]

I'm back.
I first used it on a table with "not so many rows" and it went well.
I then tried it on a table with 4000 rows and it goes terribly slow: 82 seconds to extract 300 rows out of 4000.

Is there a faster way to do this?

 

[towerbase]

My experience is that slow queries are usually caused by not having an index on the appropriate columns.

Do you have an index on column1, column2 and column3 in both your tables t1 and t2?

You can easily add an index with something like

ALTER TABLE [i]table[/i] ADD INDEX([i]column1[/i])

Andrew
Hampshire, UK

 

[sirugo]

Of course...it should have crossed my mind.
I'll try it out and report back.
Thanks.

 

[sirugo]

The query now runs 160 times faster (less than a second)!
Thanks again.