Mysql query solution 1

[JimFL]

I have a query below that would work perfectly well in TSQL but is not allowed in Mysql - does anybody know how to produce this using Mysql syntax. I want to get a max value from another table and produce it as a single field value in my result set. Can this be done?



SELECT BID,x,y (

SELECT max( aID )
FROM a
) AS z

FROM B
WHERE x = 1

 

[JimFL]

also if my aID is of type float. How do I export the data as for example 10.00 to two decimal places even though it might just be 10 rather than 10.25?

 

[TonyGroves]

How about:
[tt]
SELECT bid,x,y,FORMAT(z,2) z2
FROM
b
JOIN (SELECT MAX(aid) z FROM a) m
WHERE x=1
[/tt]
The field z2 will be formatted in the form 9,999.99 .

 

[JimFL]

Ok Thanks Tony,

Here is my actual query but its not working?


SELECT b.invoiceID, FORMAT( z, 2 ) z2
FROM ARTIST_INVOICE b
JOIN (


SELECT MAX( artistID ) z
FROM ARTIST
)m
WHERE artistID = 11

MySQL said:


You have an error in your SQL syntax near '(
SELECT MAX( artistID ) z
FROM ARTIST )m
WHERE artistID = 11' at line 3

Any ideas where it is going wrong?

 

[TonyGroves]

What MySQL version are you using? Subselects like that only became possible with v. 4.1.

 

[JimFL]

I think its version 2.5.2 which is a bit of a problem. can it be done another way for this version?

 

[TonyGroves]

2.5.2 is really ancient. You should really try to upgrade. You could probably use two queries:
[tt]
SELECT @z:=MAX(aid) FROM a;

SELECT bid,x,y,FORMAT(@z,2) z2
FROM b
WHERE x=1;
[/tt]

 

[JimFL]

Actually sorry that was the version of my PHP my Admin . But I think Mysql is v 4.0.

Thanks for your help the post above has resolved both issues for my version.

Cheers