SUB SELECTing FROM a GROUP
Posted: Fri Jun 18, 2004 2:05 pm
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.
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
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 DESCThanks