Here's one without a subquery, so may be master: select u.login, s.stop_time from users as a inner join stats as s on s.user_id = u.user_id where status = '3' and next_plan_id is null and stop_time < now() - interval '1 month' group by u.user_id, u.login, s.stop_time order by s.stop_time
On 2/2/08, rihad <[EMAIL PROTECTED]> wrote: > > Hi all, > > The situation: there are users in one table, and their access statistics > in the other. Now I want to find users whose last access time was more > than one month ago. As I've only had to write quite simple queries > involving no sub-selects so far, I'd like to ask your opinion if this > one scales at all or not. > > SELECT u.login,last_use_time > FROM users u > JOIN (SELECT user_id, MAX(stop_time) AS last_use_time > FROM stats > GROUP BY user_id) AS s ON (u.id=s.user_id) > WHERE status='3' AND next_plan_id IS NULL > AND last_use_time < now() - interval '1 month' > ORDER BY last_use_time; > > It seems to do the job, but how good is it in the long run? Any way I > could tweak it? > > Thanks. > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > -- Sincerely yours, Olexandr Melnyk <>< http://omelnyk.net/