SQL: Filtering on a computed column

Question

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?

Answer 1

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