SQL - select rows remove duplicates from one column and order by

Question

I got a table (games) like this:

ID| title |category|rank
________________________
11|FIFA 17|  ps3   | 3
22|FIFA 17|  ps4   | 2
33|FIFA 17|xbox one| 4
44| GTA 5 |  ps3   | 3
55| GTA 5 |  ps4   | 2
66| GTA 5 |xbox one| 4
77|FIFA 16|  ps3   | 3
88|FIFA 16|  ps4   | 2
99|FIFA 16|xbox one| 4

I want all rows where title contains FIFA, but no duplicates! And ordered by rank ASC so this:

ID| title |category|rank
________________________
22|FIFA 17|  ps4   | 2
88|FIFA 16|  ps4   | 2

What I tried:

SELECT * FROM games WHERE title LIKE '%FIFA%' GROUP BY title ORDER BY rank ASC

and the duplicates from title are getting removed but ORDER BY rank ASC is complete ignored, the result is like:

ID| title |category|rank
________________________
11|FIFA 17|  ps3   | 3
99|FIFA 16|xbox one| 4

EDIT:

I WANT ALL TITLES ONLY ONCE NAMELY WHERE THE RANK IS THE LOWEST!!!

@AnnaLederle and which rank should the query take into account when the same title with multiple ranks are in your data? And which category? Pls think through the problem before asking a question. — Shadow, Dec 21 '17 at 21:22
"And which category" did I mentioned that I only want specific category? — Anna Lederle, Dec 21 '17 at 21:34
Yes, you need to specify how you want to handle ranking and category. We are not mind readers and mysql is not a mind reader either. — Shadow, Dec 21 '17 at 21:48
@AnnaLederle Often the best way to answer questions on this site is to look at the expected output and write a query to do it. That's because a lot of times there is an easier approach to what the OP is trying to do. That's why people are focusing on `category` and `ID`, or in the case of my answer `MIN(rank)`, because it seems like a simpler way to solve your problem. Be understanding that we are just random people taking the time to try and help. — Aaron Dietz, Dec 21 '17 at 22:08
I already tried to keep it simple but that was too simple, now I try it more "advanced" and it's not right again. I don't know how the hell else to explain it. This is realy plain simple I want all titles just one time namely where the rank is the lowest — Anna Lederle, Dec 21 '17 at 22:20
The 2nd duplicate link shows the use for max(), but the theory is the same and there the accepted answer has a lot more detailed explanation. Btw, your original question did not say that you wanted records where the rank is the lowest in their group. — Shadow, Dec 21 '17 at 22:29

Aaron Dietz · Accepted Answer · 2017-12-21T22:48:18.583

1

I think you just want to see the rows with the lowest rank for each title... here is one way:

SELECT b.Id, b.title, b.category, b.rank
FROM (SELECT title, MIN(rank) as LowestRank
      FROM games
      GROUP BY title) A
JOIN games B on B.Title = A.Title
            and A.LowestRank = B.rank
WHERE B.title LIKE 'FIFA%'

edited Dec 21 '17 at 22:48

answered Dec 21 '17 at 21:27

Aaron Dietz

10,137
1
14
26

In this case this question is a duplicate – Shadow Dec 21 '17 at 21:30
@Shadow Yeah I'm happy to mark it as one if so. – Aaron Dietz Dec 21 '17 at 21:34
I don't get any results with it :/ – Anna Lederle Dec 21 '17 at 22:11
@AnnaLederle the method demonstrated in this answer is one of the standard solutions for your question (btw your question gets answered at least once a day...). – Shadow Dec 21 '17 at 22:33
@AnnaLederle Hey Anna, try again now. I mixed up the aliases, but it's fixed now. Side note, if anything comes before `FIFA` then add the extra `%` back in. `FIFA%` will use indexes, `%FIFA%` will not, and it didn't seem needed so I remove it in my answer. – Aaron Dietz Dec 21 '17 at 22:48
nope still no results :/ – Anna Lederle Dec 21 '17 at 23:07
@AnnaLederle Check this out.. Not sure what is happening on your end, but this works with your sample data http://sqlfiddle.com/#!9/f222b25/1/0 – Aaron Dietz Dec 22 '17 at 13:55
Yes there was a little mistake on my side. Works great, thanks! :D – Anna Lederle Dec 23 '17 at 21:37

Gordon Linoff · Answer 2 · 2017-12-22T02:46:49.180

0

I might suggest:

SELECT g.*
FROM games g
WHERE g.title LIKE '%FIFA%' AND
      g.rank = (SELECT MIN(g2.rank) FROM games g2 WHERE g2.id = g.id);

edited Dec 22 '17 at 02:46

answered Dec 21 '17 at 21:19

Gordon Linoff

1,242,037
58
646
786

In this case the question is a duplicate – Shadow Dec 21 '17 at 21:30
It don't remove duplicate titles nor sort by rank ASC. I clearly stated what I need in my question – Anna Lederle Dec 21 '17 at 21:44

score 0 · Answer 3 · answered Dec 21 '17 at 21:21

0

Based on your output. it looks l ike you only want PS4 and not ps3. Correct? Try this:

SELECT * FROM games WHERE title LIKE '%FIFA%' 
 and category = 'ps4'
GROUP BY title ORDER BY rank ASC

answered Dec 21 '17 at 21:21

WWaldo

213
3
12

SQL - select rows remove duplicates from one column and order by

3 Answers3