按最高值的顺序选择不同的行 - Select distinct rows order by highest values

Select distinct rows order by highest values

本文关键字：顺序最高值选择 | 更新日期: 2023-09-27

我有一个类似的表

Name | Image | Points | Country
-------------------------------
Bob  | a.jpg | 100    | USA
Bob  | b.jpg | 56     | USA
Sal  | c.jpg | 87     | UK
Jim  | d.jpg | 34     | UK
Bet  | e.jpg | 23     | USA
Bren | f.jpg | 5      | USA
Bren | g.jpg | 15     | UK
Test | h.jpg | 10     | USA

我想根据国家是"美国"的"积分"列获得最高的4行，并删除重复的"姓名"，所以想要的结果是

Name | Image | Points | Country
-------------------------------
Bob  | a.jpg | 100    | USA
Bet  | e.jpg | 23     | USA
Test | h.jpg | 10     | USA
Bren | f.jpg | 5      | USA

如有任何帮助，我们将不胜感激。

DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(image VARCHAR(12) NOT NULL PRIMARY KEY
,name VARCHAR(12) NOT NULL
,points INT NOT NULL
,country VARCHAR(12) NOT NULL
);
INSERT INTO my_table VALUES
('a.jpg','Bob' ,100,'USA'),
('b.jpg','Bob' , 56,'USA'),
('c.jpg','Sal' , 87,'UK'),
('d.jpg','Jim' , 34,'UK'),
('e.jpg','Bet' , 23,'USA'),
('f.jpg','Bren',  5,'USA'),
('g.jpg','Bren', 15,'UK'),
('h.jpg','Test', 10,'USA');
SELECT a.* 
  FROM my_table a 
  JOIN 
     ( SELECT name,MAX(points) points FROM my_table WHERE country = 'USA' GROUP BY name ) b 
    ON b.name = a.name 
   AND b.points = a.points 
 ORDER 
    BY points DESC 
 LIMIT 4;
+-------+------+--------+---------+
| image | name | points | country |
+-------+------+--------+---------+
| a.jpg | Bob  |    100 | USA     |
| e.jpg | Bet  |     23 | USA     |
| h.jpg | Test |     10 | USA     |
| f.jpg | Bren |      5 | USA     |
+-------+------+--------+---------+

select table.* from table join 
(select Name, max(Points) mp from table where Country='USA' group by Name
order by mp desc limit 4) t
on table.Name = t.Name and table.Points = t.mp and table.Country='USA'
order by table.points desc

当一个人的最大点行有两个记录时，例如bob的b.jpg也是100，这将导致bob结果中的多行。

试试这个：

SELECT Name,Image,MAX(points),country 
FROM table_1 
ORDER BY points desc 
GROUP BY Name,points
LIMIT 4