Hi there, I have a huge statement doing a calculation and returning a userid with a ranking. This workes fine for one table. But due to normalisation I do have 2. table containing languages of the users. Up to 3 languages for each user.
The problem is, that I do get the same user 3 times with a different ranking. How can I get rid of the 2 lower ones and just keep the one with the highest ranking? I tryed around with max, but did not have any success. If I do leave only :GROUP BY u.user_id it returns only this user if the first language scores.So ... where to now?? Here is a sniplet of my statement: SELECT DISTINCT u.userid if(u.country='gm',25,0) + if(u.age='4',25,0) + if(u.sex='2',25,0) + if(l.language_id='en',25,0) AS ranking FROM data.user u, data.user_languages l, INNER JOIN db.countries AS c ON c.country_code = u.country /* countries */ LEFT JOIN db.cities AS ct ON ct.ID = u.city /* cities */ WHERE u.user_id = l.user_id /* because it is a different table */ AND u.country = 'gm' AND u.age = '4' AND u.sex = '2' /* for the mandatory fields */ GROUP BY u.user_id, l.language_id HAVING ranking > 0 /* dont return all users! */ ORDER BY ranking desc LIMIT 0,5 Thanx for any help guys, Andy -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
