MySQL: Selecting the rows having min value of a computed column

Question

The naive way of doing this that comes to mind would be:

SELECT name, lev FROM
(SELECT name, levenshtein(name, *parameter*) as lev FROM my_table)
WHERE 
lev = (SELECT MIN(lev) FROM 
(SELECT name, levenshtein(name, *parameter*) as lev FROM my_table ));

However the "(SELECT name, levenshtein(name, parameter) as lev FROM my_table)" subquery, which is very expensive (huge table) is repeated twice which seems horribly inefficient.

I somehow though you could write :

SELECT name, lev FROM
(SELECT name, levenshtein(name, *parameter*) as lev FROM my_table) as my_temp_table
WHERE 
lev = (SELECT MIN(lev) FROM my_temp_table);

But it doesn't seem to work.

Is there a clean way to optimize that query for speed? Did I miss something obvious?

Do I have to rely on temporary tables? (trying to avoid it due to the overhead/complexity as they don't seem appropriate for very frequent/concurrent queries)

Any input from SQL ninjas would be greatly appreciated ;)

Asquint

Answer 1

select * from
(
SELECT * 
FROM `test`.`test`
)
as temp
where compute_total_price_single=(select min(compute_total_price_single))
;

This is what I did for my problem, since it worked I suspect the following would also work:

SELECT name, lev FROM
    (SELECT name, levenshtein(name, *parameter*) as lev FROM my_table) as my_temp_table
WHERE 
lev = (SELECT MIN(lev));

I'm using MySQL 5.

Nanceynanchang

I think you're missing a from ... in the where lev = (select min(lev)) part. – Janiecejanifer

It works without the FROM my_temp_table, and dose give me a syntax error(no such table) if I try to refer to the temp table in the where clause. – Nanceynanchang

Answer 2

SELECT * FROM
(
    SELECT * 
    FROM `test`.`test`
) as temp
WHERE compute_total_price_single = (SELECT MIN(compute_total_price_single));

Connor

Answer 3

If you're just trying to get one row with the minimum value, you could do:

SELECT name, levenshtein(name, *parameter*) AS lev
FROM my_table
ORDER BY lev ASC LIMIT 1;

Donyadoodad

Answer 4

SELECT name, min(levenshtein(name, *parameter)) as lev
FROM my_table
GROUP BY name;

Rumilly

And it indeed doesn't produce the desired result... :/ – Asquint