CODEWITHSUNDEEP
mysql
Luna C
Luna C

Reputation: 1

with, ifnull, question about = and := in mysql

I'm writing a statement that only present the nth highest salary For example, get the second highest salary from the following table, the result is 200.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

If I write:

declare M int;
set M = N-1;
with s as (select distinct Salary 
          from Employee
          order by Salary desc
          limit M, 1)
select ifnull(s, null) as getNthHighestSalary

it reports s not exist, but if I write:

 declare M int;
 set M = N-1;

 select ifnull(
          (select distinct Salary 
          from Employee
          order by Salary desc
          limit M, 1), null) as getNthHighestSalary

it will work. I understand "with as" creates temporary table, but I don't understand why ifnull() cannot find s.

Also, set M = N-1 and M:= N-1 have the same result, could anyone please explain to me that ":= or =" which is better?

Upvotes: 0

Views: 18

Answers (1)

ysth
ysth

Reputation: 98388

s is the table alias, the column to pass to ifnull is s.Salary.

SET can take either = or :=, there is no difference.

Upvotes: 1

Related Questions