Page 1 of 1

SUB SELECTing FROM a GROUP

Posted: Fri Jun 18, 2004 2:05 pm
by anjanesh
I have a query statement that does gets the news item of each user.
But I want ONLY one news from each user(roll). The table contains many users' many news items. A user's latest news item is the one which is latest by date(newstime). Therefore I've to pick the latest one from each user(roll). I tried this but this is not showing the latest news of each user - it is showing the first.

Code: Select all

SELECT
 a.news AS news,
 a.roll AS roll,
 a.newstime AS newstime,
 FROM studentsnews a, students b
 WHERE a.roll=b.roll
 GROUP BY a.roll
 ORDER BY a.newstime DESC
If I change the GROUP BY to this : GROUP BY a.roll, a.newstime DESC then I get multiple entries of the same roll. Any idea how to get this done ? I tried DISTINCT but no use.
Thanks

Only 4.1 and above version support SUB SELECTS

Posted: Tue Jun 22, 2004 1:25 pm
by anjanesh
Man ! Cannot believe that no one replied to this.
Only 4.1 and above version support SUB SELECTS
Just found that this works in MySQL 4.1 and greater.

Code: Select all

SELECT 
news, 
roll, 
newstime, 
FROM studentsnews a
WHERE newstime=(SELECT MAX(b.newstime) FROM studentnews b WHERE a.roll=b.roll)
ORDER BY newstime DESC
For 4.0 and below :

Code: Select all

SELECT
 a.roll AS roll,
 SUBSTRING(MAX(CONCAT(newstime,news)),15) AS news,
 MAX(a.newstime) AS newstime,
 FROM studentsnews a, students b
 WHERE a.roll=b.roll
 GROUP BY a.roll
 ORDER BY newstime DESC