开发者

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)...
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜