SQL: Filtering on a computed column

mysql, sql

Solution

You can't refer to `no_dirs` in the `WHERE` clause because the `WHERE` is evaluated before the `SELECT` clause, so `no_dirs` does not exist yet.

Also you can't use `MIN` like that. This is a solution with a subquery:

SELECT length(b) - length(replace(b, '/', '')) no_dirs, b
FROM tbl1
WHERE b like "%path%"
  AND length(b) - length(replace(b, '/', '')) = (
                      SELECT MIN(length(b) - length(replace(b, '/', ''))) 
                      FROM tbl1
                      WHERE  b like "%path%" )

See it working on SQLFiddle

Problem

I have a problem that can't be that hard to solve, but I'm having problems with it. A small table ``` create table tbl1 ( a INT AUTO_INCREMENT NOT NULL UNIQUE PRIMARY KEY, b VARCHAR(100) ); ``` and some data ``` insert into tbl1 (b) values ("/some/path/1"), ("/some/path/2"), ("/some/longer/path/1"), ("/some/longer/path/2"), ("/some/way/1"), ("/some/longer/way/2"); ``` The I want to query like this: ``` select length(b) - length(replace(b, '/', '')) no_dirs, b from tbl1 where b like "%path%" and no_dirs = min(no_dirs); ``` which should return all rows where no_dirs has its miminum value. The last condition does not work. I also fiddled with a subquery, but could not get that to work. Suggestions?

Original source