CODEWITHSUNDEEP
phpmysql
EvilNabster
EvilNabster

Reputation: 516

Php selecting one of each kind

For example i have lots of users, each of them has selected the game they like, now in the same mysql row they have recommends (thumbs up). Example Database:

(nickname): Leonard, (recommends): 999, (game): Mario; (nickname): Nikky, (recommends): 1999, (game): Counter-Strike (nickname): Sam, (recommends): 199, (game): AION

Now what i want to do is select the most recommended user for each game without doing double mysql requests, is that possible?

SELECT * FROM users ORDER BY recommends DESC

This is just showing all of them sorted by the recommends amount but what i want is to show 1 with the most recommends for each game because there are many users that have bunch of recommends for the same game.

Upvotes: 0

Views: 56

Answers (5)

fusion3k
fusion3k

Reputation: 11689

Edit: MySQL 5.7

Despite being accepted, in the original response (see below) the proposed code with MySQL 5.6 does not produce the desired result, and from MySQL 5.7 it is not executable.

I recommend this approach instead:

SELECT a.*
FROM users a 
LEFT JOIN users b 
ON a.game = b.game AND a.recommends < b.recommends
WHERE b.recommends IS NULL;

By performing a LEFT_JOIN between a and b when a.game is equal to b.game and a.recommends is less than b.recommends we obtain a set in which higher values of a.recommends are associated with null values of b.recommends. Something like this:

a.nickname  a.recommends  a.game     b.recommends
----------  ------------  ---------  ------------
will        100           mariobros  110
mary        50            cart       80
john        110           mariobros  800
will        100           mariobros  800
martin      80            cart       null
viola       800           mariobros  null

With WHERE b.recommends IS NULL we restrict the selection to the rows we are interested in. This way if there are two nicknames with the same score in the same category they are both selected, which was not the case in the previous code.

dbfiddle demo

Original Answer:

You can simply prepend GROUP BY to ORDER BY:

SELECT * FROM users GROUP BY game ORDER BY recommends DESC

SqlFiddle demo

Upvotes: 2

Ligerien
Ligerien

Reputation: 1

If I understand, it seems that you want is :

SELECT * FROM users group by game ORDER BY recommends DESC

group by makes that only one user per game is retrieved and order by retrieves the user with the high score.

Upvotes: 0

Klemen Tusar
Klemen Tusar

Reputation: 9689

SELECT t1.*
FROM users AS t1
LEFT JOIN users AS t2
ON t1.game = t2.game AND t1.recommends < t2.recommends
WHERE t2.recommends IS NULL

Fiddle demo: http://sqlfiddle.com/#!9/43486/13/0

Upvotes: 0

OZI
OZI

Reputation: 434

You can use group by. 

SELECT users.*, MAX(user.recommends) AS maxrec GROUP BY game;

Upvotes: 1

Patrick Moore
Patrick Moore

Reputation: 13344

You can use GROUP BY clause to combine multiple records based on one column, and COUNT() to count. Hard without seeing your full schema, but something like:

SELECT users.*, COUNT(users.recommends) AS total_recommends FROM users ORDER BY recommends DESC GROUP BY game

Upvotes: 1

Related Questions