SELECT id, bug_id, reporter_id, bugnote_text_id
FROM bugnote_table P1
WHERE bugnote_text_id = (SELECT MAX(bugnote_text_id) FROM bugnote_table P2 WHERE P2.id = P1.id)


I got error #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT MAX(bugnote_text_id) FROM bugnote_table P2 WHERE #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT MAX(bugnote_text_id) FROM mantis_bugnote_table P2 WHERE




Somebody correct it....help

What version of MySQL are you using?? You need at least version 5 to use subqueries.

But you know, there is NO reason to use a subquery for this simple question.

Thank you very much for your kindly adviced and guide Old Pedant,

I am using My SQL 4.0.26, for your known I am using multiple tables and try to capture all fields need related with MAX(bugnote_text_id) field.

When I used code below, GROUP BY i.id, it come out with first record bugnote_text_id but I only want the MAX record of bugnote_text_id to come out.


SELECT i.id AS 'id', iii.note AS 'Solution', iv.value AS 'State'
FROM bug_table i, bugnote_table ii, bugnote_text_table iii, custom_field_string_table iv
WHERE i.id = ii.bug_id
AND ii.bugnote_text_id = iii.id
AND i.id = iv.bug_id
AND iv.field_id = 5
AND iv.value = 'KL'
AND i.date_submitted > '2010-08-01'AND i.date_submitted < '2010-09-01'
GROUP BY i.id
ORDER BY i.id ASC


Any advice with it.
Regards.

Well, this *MIGHT* work:

But, really, my advice is still to move to MySQL 5.1.

I will try yours, since now i couldnt get through the set will server...I hope it will produce every rows with MAX(ii.bugnote_text) and follow with other fields related.

Even I am using My SQL version 4.0, hope I can get ideas and solution how to workaround with it.


Thanks Old Pedant for your kindly help.

After tried code given, output only came out as 1 row because of using LIMIT 1.

The problem is 1 id got 100 bugnote_text_id together with fields related and i have 8000 id in table looking for it MAXIMUM bugnote_text_id each together with fields related.. (8000 rows).

But how to workaround with My SQL version 4.0, because I dont have any authority to upgrade 5.1.

Do anybody have ideas hellppppppppppppppppppp....

The best I can see to do is to use a SERIES of queries.

But just a guess.

First of all, thanks Old Pedant for your great ideas, I already got output that I need, using Temporary Table created as yours.But now, when I try to download to MS EXCEL, OUTPUT generated, errors occured said that temporary table used was not exitst.I warned by management not to create permanent table in database, just use it :(Any ideas to download it to MS EXCEL ? Happy to get the output but now I couldnt download it....arrgghhhh....what a world :)

A temporary table only exists as long as the connection exists. If you close the connection and open another one, then the temp table is gone.

I would *assume* that whatever code you are doing to create the EXCEL file is using a different connection. So... If at all possible, just move all the above code onto the SAME CONNECTION that you use to create the EXCEL data.

Ok Okd Pedant, I code it using INTO OUTFILE 'f:/result.xls' in your example, file couldnt be create.... anything wrong here...

DROP TEMPORARY TABLE IF EXISTS top_bugnotes;

CREATE TEMPORARY TABLE top_bugnotes (
bug_id INT,
max_text_id INT );

INSERT INTO top_bugnotes( bug_id, max_text_id )
SELECT bug_id, MAX(bugnote_text_id ) FROM bugnote_table
GROUP BY bug_id;

SELECT B.id, BNT.note AS 'Solution', CFS.value AS 'State'

INTO OUTFILE 'f:/result.xls'

FROM bug_table AS B,
bugnote_table AS BN,
top_bugnotes AS TB,
bugnote_text_table AS BNT,
custom_field_string_table AS CFS
WHERE BN.bug_id = B.id
AND ( TB.bug_id = BN.bug_id AND TB.max_text_id = BN.bugnote_text_id)
AND BNT.id = BN.bugnote_text_id
AND ( CFS.bug_id = B.id AND CFS.field_id = 5 AND CFS.value = 'KL' )
AND ( B.date_submitted > '2010-08-01' AND B.date_submitted < '2010-09-01' )DROP TEMPORARY TABLE IF EXISTS top_bugnotes;

CREATE TEMPORARY TABLE top_bugnotes (
bug_id INT,
max_text_id INT );

INSERT INTO top_bugnotes( bug_id, max_text_id )
SELECT bug_id, MAX(bugnote_text_id ) FROM bugnote_table
GROUP BY bug_id;

SELECT B.id, BNT.note AS 'Solution', CFS.value AS 'State'
FROM bug_table AS B,
bugnote_table AS BN,
top_bugnotes AS TB,
bugnote_text_table AS BNT,
custom_field_string_table AS CFS
WHERE BN.bug_id = B.id
AND ( TB.bug_id = BN.bug_id AND TB.max_text_id = BN.bugnote_text_id)
AND BNT.id = BN.bugnote_text_id
AND ( CFS.bug_id = B.id AND CFS.field_id = 5 AND CFS.value = 'KL' )
AND ( B.date_submitted > '2010-08-01' AND B.date_submitted < '2010-09-01' )