Use a select result and compare it

Question

My question:

How do I get MySQL to use in a select a previouly selected value without repeating itself

My Answer, taken from another post

Set a variable direclty on the SELECT like this:

SELECT @q:= ##longQuery## AS a, (@q>2) AS b;

I had this MySQL:

SELECT ##longQuery## AS a, (a>2) AS b;

but I got:

ERROR 1054 (42S22): Unknown column 'a' in 'field list'

I didn't want to repeat the ##longQuery##, because it is actually quite lengthy on the real code.

Thank you

Please format code by indenting with four spaces. It makes it much easier to read. — tadman, Apr 03 '13 at 19:25

score 2 · Accepted Answer · answered Apr 03 '13 at 19:23

2

Two possible solutions:

The subquery solution

select a, a>2 as b
from (select 1 as a) as q;

The 'variable' solution

set @a = 1;
select @a as a, @a>2 as b;

answered Apr 03 '13 at 19:23

Barranka

You are repeating "a". I need to, either to not repeat "a" (or in my example, "1"), or find a way that MySQL understands "a" as a value previously selected. – Florius Apr 03 '13 at 19:30
1

@Florius then go with the subquery solution... You cannot reference in a `select` a column name defined as an alias in the same `select`. Anyway, both approaches work... the second approach is split in a two-step process, but it works – Barranka Apr 03 '13 at 19:34
@Florius if `a` is the result of a subquery (as your edit suggests), then you can also store the result of that subquery in a variable: `set @a = (select ...)`. Of course, this 'select' *must* return a single column and a single row – Barranka Apr 03 '13 at 19:36
I found a way to define a variable inside the SELECT, thanks. – Florius Apr 03 '13 at 19:46

Matt Busche · Answer 2 · 2013-04-03T19:26:08.567

0

Using a subQuery will work

SELECT a, (a>2) AS b
FROM (
  SELECT 1 AS a
) f

edited Apr 03 '13 at 19:26

answered Apr 03 '13 at 19:20

Matt Busche

2 Answers2