List:General Discussion« Previous MessageNext Message »
From:Alex Gemmell Date:November 30 2005 3:06pm
Subject:Rewriting subquery for old MySQL
View as plain text  
Hi,

Unfortunately I need to use a query on an old MySQL (4.0.xx) and the one 
I currently have uses a subquery.  So it works on my 4.1 but not with 
this 4.0.  I have read in the MySQL manual that I can rewrite subqueries 
using joins but I don't see how with this query because I only have one 
table to check.

Basically all I'm doing is pulling out the most recent 10 activities 
stored in a table, then reversing the order so it appears oldest first, 
to newest (note the "$row['Assignment_ID']" is just a PHP variable):

SELECT * FROM
(SELECT * FROM tblactivities
  WHERE Assignment_ID=$row['Assignment_ID']
  ORDER BY Date DESC LIMIT 10) AS tblTemp
ORDER BY Date ASC

This works perfectly on 4.1.  How can I rewrite this to get the same 
effect without using a subquery and so allow it to work on 4.0?

Any help gratefully recieved!

Alex
Thread
Rewriting subquery for old MySQLAlex Gemmell30 Nov
  • Re: Rewriting subquery for old MySQLSGreen30 Nov
    • Re: Rewriting subquery for old MySQLAlex Gemmell30 Nov
      • Re: Rewriting subquery for old MySQLrouvas30 Nov
        • Re: Rewriting subquery for old MySQLAlex Gemmell30 Nov
        • Re: Rewriting subquery for old MySQLAlex Gemmell30 Nov
        • Re: Rewriting subquery for old MySQLSGreen30 Nov
      • Re: Rewriting subquery for old MySQLDan Nelson30 Nov
        • Re: Rewriting subquery for old MySQLAlex Gemmell30 Nov
          • Re: Rewriting subquery for old MySQLMichael Stassen30 Nov
      • Re: Rewriting subquery for old MySQLMichael Stassen30 Nov
        • Re: Rewriting subquery for old MySQL - SOLVEDAlex Gemmell30 Nov
          • Re: Rewriting subquery for old MySQL - SOLVED - correction!Alex Gemmell6 Dec