Column not found in Select statement inside select statement

Question

Trying to select one row at a time from this query (for example where the rank = 1). However, it doesn't work because "unknown column 'rank'. if i change it to "WHERE id = 1" then it works, but for some reason it doesn't know what rank is even tho it is set

    SET @rownum=0;

  SELECT @rownum := @rownum + 1 AS rank, id, client, date, time, pickupCity, pickupState
  FROM (
        SELECT r.id, CONCAT(c.fname, ' ', c.lname) as client, r.date,
    LOWER(TIME_FORMAT(r.time, '%l:%i%p')) as time, r.pickupCity,               r.pickupState 

    FROM request r  
    INNER JOIN client c  ON r.client = c.id 
    INNER JOIN pickup p ON r.id = p.request
    INNER JOIN driver d ON d.id = p.driver

    WHERE date = '2018-04-18' AND d.id = 1
    GROUP BY time

  ) AS tab
    HAVING rank = 1;

here `SELECT @rownum := @rownum + 1 AS rank` then you are doing `where rank=1` .. this wrong, remove the where as it is not needed — Moudiz, Apr 05 '18 at 14:27
I think you have confirmed that the where clause is executed before the select in the order of execution https://stackoverflow.com/questions/24127932/mysql-query-clause-execution-order — P.Salmon, Apr 05 '18 at 14:28

score 2 · Accepted Answer · answered Apr 05 '18 at 14:38

In MySQL, you can do this using HAVING:

SELECT @rownum := @rownum + 1 AS rank, id, client, date, time, pickupCity, pickupState
FROM (SELECT r.id, CONCAT(c.fname, ' ', c.lname) as client, r.date, 
             LOWER(TIME_FORMAT(r.time, '%l:%i%p')) as time, r.pickupCity, 
             r.pickupState
      FROM request r JOIN
           client c
           ON r.client = c.id JOIN
           driver d
           ON ?
           pickup p
           ON d.id = p.driver
      WHERE date = '2018-04-18' AND d.id = 1
      GROUP BY time
     ) t CROSS JOIN
     (SELECT @rank := 0) params
HAVING rank = 1;

Notes:

The ?? is for the missing JOIN conditions.
I went through the effort of fixing your JOIN syntax. You should go through the effort of learning proper, explicit, standard JOIN syntax.
You can set @rank in the query; you don't need a separate statement.
The GROUP BY makes no sense, because you have many unaggregated columns in the SELECT.

If I had to speculate, the root cause of your problems is the missing JOIN condition, and you've put a lot of effort into working around that.

That worked however, for some reason if I want rank=1 then I get when rank is actually 2. Its always one higher than what I am looking for — Jordan Farris, Apr 05 '18 at 16:33
@JordanFarris . . . Perhaps you have an unexpected value that is first . . . such as `NULL`. — Gordon Linoff, Apr 06 '18 at 01:56

Column not found in Select statement inside select statement

1 Answers1