[RESOLVED] Complex ORDER BY statement help!...
Here's my problem :
I have one table called "users" that has a list of users (fields : ID, Name, Country, etc.) and another table called "user_ratings" that has a list of ratings that each user recieved (fields : USER_ID, RATING).
In order to get a users rating i would use :
SELECT AVG(RATING) from user_ratings, users
WHERE user_ratings.USER_ID = users.ID
AND users.ID = 3
I need to know how i would retrieve all the users from the database ordered by their ratings...??
I tried searching the net, but couldn't find anything useful enough,
Thanks in Advance!
[617 byte] By [
gilly914] at [2007-11-20 10:22:28]
# 1 Re: [RESOLVED] Complex ORDER BY statement help!...
I can't say much more than I did in my other post... here's the query adapted for your above example:
SELECT
u.UserID, u.Name, AVG(ur.Score) AS Rating
FROM test_users u
LEFT JOIN test_user_ratings ur USING (UserID)
GROUP BY u.UserID
ORDER BY Rating DESC
If you want to try that exact query out, here's my test setup:
CREATE TABLE test_users (
UserID int(10) unsigned NOT NULL auto_increment,
`Name` varchar(20) NOT NULL,
PRIMARY KEY (UserID)
) ENGINE=InnoDB;
INSERT INTO test_users (UserID, Name) VALUES
(1, 'Andreas'),
(2, 'Some guy'),
(3, 'Some other guy'),
(4, 'Yet another guy');
CREATE TABLE test_user_ratings (
UserID int(10) unsigned NOT NULL,
Score tinyint(4) NOT NULL,
KEY UserID (UserID)
) ENGINE=InnoDB;
INSERT INTO test_user_ratings (UserID, Score) VALUES
(1, 9),
(1, 10),
(2, 5),
(2, 5),
(2, 7),
(3, 2),
(3, 1);
ALTER TABLE test_user_ratings
ADD CONSTRAINT test_user_ratings_ibfk_1 FOREIGN KEY (UserID) REFERENCES test_users (UserID) ON DELETE CASCADE ON UPDATE CASCADE;
# 2 Re: [RESOLVED] Complex ORDER BY statement help!...
other database would require to include u.Name in the Group-by list, so i usually change the query to..
SELECT
u.UserID, MAX(u.Name) AS UserName, AVG(ur.Score) AS Rating
FROM test_users u
LEFT JOIN test_user_ratings ur USING (UserID)
GROUP BY u.UserID
ORDER BY Rating DESC
or
SELECT
u.UserID, u.Name, AVG(ur.Score) AS Rating
FROM test_users u
LEFT JOIN test_user_ratings ur USING (UserID)
GROUP BY u.UserID, u.Name
ORDER BY Rating DESC
# 3 Re: [RESOLVED] Complex ORDER BY statement help!...
ALTER TABLE test_user_ratings
ADD CONSTRAINT test_user_ratings_ibfk_1 FOREIGN KEY (UserID) REFERENCES test_users (UserID) ON DELETE CASCADE ON UPDATE CASCADE;
What does this do...??
I've never seen this before...
I never create tables manually, i usually use MySQL Administrator or phpMyAdmin.
Can i run that query without that line (just in case i have some kind of problem with it)
Thanks!
# 4 Re: [RESOLVED] Complex ORDER BY statement help!...
Yes, you don't have to create foreign keys in your table for it to work. Foreign keys are just data integrity constraints that make sure that the users that the user_ratings table refers to actually exist, and if a user is deleted, the ratings for that user are also deleted automatically.
# 5 Re: [RESOLVED] Complex ORDER BY statement help!...
Well, the combination between the two solutions i got from andreasblixt and Thread1 worked great!,
BUT now i have another small problem...
The query doesn't retrieve any user that doesn't have a rating at all, only those that do have ratings...
How can i solve this problem...?
# 6 Re: [RESOLVED] Complex ORDER BY statement help!...
The query in my first post in this thread returns users with no rating (NULL). What query and database engine are you using?
# 7 Re: [RESOLVED] Complex ORDER BY statement help!...
sorry... my mistake!
the truth is it works great!
Thanks A Million... :)
