displaying a certain value 1

[kaptlid]

say I have a table with two columns and the values:
4 3
7 2
12 4

how do I get the table to take max(col1-col2) and display it in every row.

as in:

4 3 8
7 2 8
12 4 8

Thanks

I need a query for versions 4.1 and 3.23,

 

[TonyGroves]

For version 4.1, you could use a subquery to get the max value, then use that result in the outer query. However, version 3.23 does not support subqueries, so you would have to use a temporary table instead.

 

[kaptlid]

I get a null result trying this query:

SELECT `1`, `2`, (select max( `1`-`2`)) as maxvalue FROM `table` group by `1`

 

[TonyGroves]

The subquery has to be a full query returning a single value. Write that first and test it, then incorporate it into your query. Also, you're not doing any grouping, so you don't need the grouping clause.

 

[feherke]

Hi

Put the sub-[tt]select[/tt] into the [tt]from[/tt] part of the outer [tt]select[/tt], not in the field part.

[gray]-- slower[/gray]
[b]select[/b]
col1,col2,(
  [b]select[/b]
  max(col1-col2)
  [b]from[/b] thetable
) maxvalue
[b]from[/b] thetable

[gray]-- faster[/gray]
[b]select[/b]
col1,col2,maxvalue
[b]from[/b] thetable
,(
  [b]select[/b]
  max(col1-col2) maxvalue
  [b]from[/b] thetable
) foo

Feherke.

http://rootshell.be/~feherke/

 

[kaptlid]

I have another question, how do I use the column maxvalue to do operations with the other columns?

select
col1,col2,(
select
max(col1-col2)
from thetable
) maxvalue, maxvalue-col1
from thetable

I keep getting an error saying maxvalue does not exist.

Regards,

 

[feherke]

Hi

What about using the faster code ?

select
col1,col2,maxvalue[red],maxvalue-col1[/red]
from thetable
,(
  select
  max(col1-col2) maxvalue
  from thetable
) foo

Feherke.

http://rootshell.be/~feherke/

 

[kaptlid]

that worked. One question, do I have to declare the field name before I use it in the query or subquery?

 

[feherke]

Hi

do I have to declare the field name before I use it in the query or subquery?

Your code did not worked because you tried to use an alias in an expression in the same [tt]select[/tt]'s field list where the alias itself is defined. My code worked because I defined the alias in the inner [tt]select[/tt] and used it only in the outer [tt]select[/tt], not in the same one. If this was the question...

Feherke.

http://rootshell.be/~feherke/