Friday, November 30, 2007

Tricks to use LIMIT clause within subquery

Well, I try to use LIMIT clause in my subquery. It doesn't work with MySQL 5, and the error message states that: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'. Here is my first query:

SELECT * FROM user_activities WHERE DATE(activity_date)
IN (
SELECT
DISTINCT DATE(activity_date)
FROM user_activities
WHERE
user_id = 1069
ORDER BY
activity_date
DESC
LIMIT 3)

So, I ask my friend dara and a code from the Internet. His idea and that code is quite similar. The trick is to use a join statement. Make the subquery that use limit clause join with the original table. Here is my second code, it works:

SELECT ua.* FROM (SELECT DISTINCT DATE(activity_date) AS activity_date FROM user_activities WHERE user_id = 1069 ORDER BY activity_date DESC LIMIT 3) AS A INNER JOIN user_activities as ua ON DATE(ua.activity_date) = DATE(A.activity_date) WHERE ua.user_id = 1069 ORDER BY ua.activity_date DESC

1 comment:

Anonymous said...

top [url=http://www.c-online-casino.co.uk/]uk online casino[/url] check the latest [url=http://www.casinolasvegass.com/]free casino[/url] free no consign reward at the foremost [url=http://www.baywatchcasino.com/]baywatchcasino
[/url].

Subscribe in a Reader