CODEWITHSUNDEEP
mysqlsubquery
Dimitris Sfounis
Dimitris Sfounis

Reputation: 2500

Keeping result of MySQL function (that gets used in WHERE) in my query results

I have got a MySQL function on my database, that calculates the levenshtein distance between two strings. levenstein("str1","str2"), returns INT

I have a table filled with strings, and I would like my query to return the most relevant rows to the input string, if any. My Query looks like this:

SELECT * FROM my_table
WHERE levenshtein('John',name) <= 15
ORDER BY date DESC`

As you see, the levenshtein distance gets used in the WHERE clause. The function itself works just fine.

But in my result, I would also like to have a column with the specific levenshtein distance of each row from "John". It gets calculated in the WHERE clause, how can I change my query to actually store it and have it come out with the rest of the results?

Upvotes: 2

Views: 82

Answers (3)

Michael
Michael

Reputation: 3729

You can put the calculation in your selection, name it, and reference it in a having clause:

SELECT
  *, levenshtein('John',name) as lev
FROM my_table
HAVING
  lev <= 15
ORDER BY
  date DESC;

Upvotes: 1

Anton
Anton

Reputation: 4052

You can use function in your list of fields

SELECT *,levenshtein('John',name) as lev FROM my_table
HAVING lev <= 15
ORDER BY date DESC

Here is the SQLFiddle that illustrates the concept: http://sqlfiddle.com/#!9/6274e2/2

Upvotes: 1

kevingessner
kevingessner

Reputation: 18985

Use a subquery, and filter the result of that:

SELECT name, score FROM (
  SELECT name, levenshtein('John', name) AS score, date
  FROM my_table
) AS t
WHERE t.score <= 15
ORDER BY date DESC

This gives you the result of the function as a plain column in the outer query, so you can filter, sort, etc by it.

Upvotes: 3

Related Questions