[RESOLVED] DISTINCT not providing unique results

schwim

Hi there everyone,

Yesterday a couple of people here helped me prune down a query to get users online from my sessions table. The problem I have is that DISTINCT no longer seems to be working for me. If someone opens two browsers to view the site, their name shows up twice in the online list. Here's the query:

	$sql="SELECT DISTINCT s.session_user_id, s.session_time, u.user_id, u.username, u.user_type, u.user_allow_viewonline FROM phpbb_sessions s INNER JOIN phpbb_users u ON u.user_id = s.session_user_id WHERE session_user_id > 1 AND session_time > $session_time AND u.user_type <> 2 ORDER BY LOWER(u.username) ASC";;

A unique session_user_id should do it, but it's not. What do I need to do to get it to work?

thanks,
json

bradgrafelman

Rather than using DISTINCT (which doesn't do what you apparently think it does, by the way), use GROUP BY on whichever column(s) uniquely identify a visitor (such as s.session_user_id, as you mentioned above).

schwim

Hi there Brad,

I think I'm implementing it incorrectly. I'm getting an ambiguous error when I add the group by clause:

	$sql="SELECT s.session_user_id, s.session_time, u.user_id, u.username, u.user_type, u.user_allow_viewonline FROM phpbb_sessions s INNER JOIN phpbb_users u ON u.user_id = s.session_user_id WHERE session_user_id > 1 AND session_time > $session_time AND u.user_type <> 2 ORDER BY LOWER(u.username) ASC GROUP BY session_user_id";;

I also tried "GROUP BY s.session_user_id". Do the clauses at the end require a certain order?

thanks,
json

schwim

It seems that order does matter. Moving group by in front of order by seems to have resolved my issue.

thanks very much for your help,
json

bradgrafelman

schwim wrote:
Do the clauses at the end require a certain order?

Yes - you have to follow the order shown in the manual for a SELECT query (e.g. GROUP BY should come before ORDER BY).