CODEWITHSUNDEEP
phpmysql
Ashley Bowman
Ashley Bowman

Reputation: 19

Select distinct rows order by highest values

I have a table like

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

I want to get the top 4 highest rows based on the "Points" column where the country is "USA" and removing duplicate "Names", so the desired outcome would be

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

Any help would be appreciated thank you

Upvotes: 0

Views: 63

Answers (3)

Abolarin stephen
Abolarin stephen

Reputation: 358

Try This:

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

Upvotes: -1

Strawberry
Strawberry

Reputation: 33945

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     |
+-------+------+--------+---------+

Upvotes: 3

amow
amow

Reputation: 2223

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

When a person's max point row have two record, eg bob's b.jpg is also 100, this would lead to multi rows in bobs result.

Upvotes: 0

Related Questions