How to select unique rows followed by row with highest value in MySQL?
I have a MySQL database table containing information about places. I'm trying to fetch all unique categories followed by place with highest rating, but results returned by server does not seems to be accurate.
In database in one category can be few records scored 100 but MySQL would select one that have score 95 for example.
Here is a query:
SELECT category, score, title
FROM places
WHERE active = '1'
GROUP BY category
ORDER BY score DESC
is it possible to do that in single query?
UPDATE
I have rewrote my query as was suggested to use MySQL function MA开发者_开发技巧X() however returned results are still wrong
here is example of new query
SELECT category, MAX(score) AS maxScore, title, score AS realScore
FROM places
WHERE active = '1'
GROUP BY category
ORDER BY score DESC
SELECT category, MAX(score) max_score
FROM places
WHERE active = 1
GROUP BY category
ORDER BY max_score DESC, RAND()
SELECT category
, MAX(score) AS maxScore
FROM places
WHERE active = '1'
GROUP BY category
If you want other fields too, besides the category
and (maximum) score
, you can use a subquery:
SELECT p.category
, p.score
, p.title
, p.otherfield
, ...
FROM
places AS p
JOIN
( SELECT category
, MAX(score) AS maxScore
FROM places
WHERE active = '1'
GROUP BY category
) AS grp
ON grp.category = p.category
AND grp.maxscore = p.score
WHERE p.active = '1'
ORDER BY maxScore DESC
As @zekms pointed, this will this will produce multiple rows (with same category) if there are several rows with the same category and max score.
Try this:
SELECT category, max(score)...
精彩评论