Order by a value returned from function called in Stored Procedure MYSQL

Question

I want to order by dist : dist is a double value returned from function called isNeighbour It throws error as dist is undefined : Unknown column 'dist' in field list

DELIMITER $$

DROP PROCEDURE IF EXISTS `connectarabs`.`maxEdges` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `maxEdges`(id int,lat double,lon double,rad double)
BEGIN


if lat is null then

set lat=(select a.latitude from account a where a.id=id);
end if;
if lon is null then
set lon=(select a.longitude from account a where a.id=id);
end if;
 SELECT friends.* FROM account friends left  join account_friendTest me on (friends.id=me.second_account)
  or (friends.id=me.first_account) where (me.first_account=id OR me.second_account=id) AND friends.id <> id AND
     (   ((select isNeighbour(lat,lon,friends.latitude,friends.longitude,rad) as dist )<rad)   ) order by dist;
END $$
DELIMITER ;

score 1 · Accepted Answer · answered Aug 27 '12 at 10:36

1

This is because you can't alias column used in WHERE clause and use that in ORDER BY clause. Instead you need to SELECT that column and use HAVING clause to filter it:

SELECT friends.*,
       isNeighbour(lat,lon,friends.latitude,friends.longitude,rad) AS dist
FROM account friends
     LEFT JOIN account_friendTest me
        ON (friends.id=me.second_account)
           OR (friends.id=me.first_account)
WHERE (me.first_account=id OR me.second_account=id) AND
      friends.id <> id
HAVING dist < rad
ORDER BY dist;

answered Aug 27 '12 at 10:36

Omesh

27,801
6
42
51

Yes,That is working Perfectly Thanks very Much but why I have to put Having dist – Youans Aug 27 '12 at 10:58
I have shifted that condition from `WHERE` clause to `HAVING` clause. If you put this condition in `WHERE` clause also, then it has to do the function computation twice, which would reduce the performance of query. – Omesh Aug 27 '12 at 11:02
1

No, It throws error I searched now and I found check it's very Interested [link] (http://stackoverflow.com/questions/942571/using-column-alias-in-where-clause-of-mysql-query-produces-an-error) – Youans Aug 27 '12 at 11:13

Order by a value returned from function called in Stored Procedure MYSQL

1 Answers1