Trouble filtering out fields w/ identical titles in MySQL, can't use DISTINCT

 SELECT america, 
       america_un, 
       game, 
       genre, 
       id, 
       platform, 
       url 
FROM   `main-games` 
ORDER  BY tally_rank 
LIMIT  3 

Basically my issue is that I may at times have two games (different data, just the same name) which have tally_rank-s which are one off from each other, giving me duplicates. Essentially what I'm wanting to do is make the game column distinct so it disregards any other games with the same name except for the first one I come across.

So if I had "game1, game2, game2, game3", I would want the first game2 and would disregard the second one.

Not sure how to do this since from what I can recall, DISTINCT is all-or-nothing in MySQL.

EDIT: I should note I've used GROUP BY game on this, but it skips every other record开发者_StackOverflow (2, 4, etc.) for some reason.

EDIT 2: Here is the correct order by tally_rank in the database:

Knight's Contract
Fight Night Champion
The Legend of Heroes: Trails in the Sky
PopCap Hits!
Killzone 3

What will happen is that the query will grab 1/3/5, and if I remove the "GROUP BY game", it grabs 1/2/3. Not really sure why "GROUP BY game" is skipping records.

When trying to GROUP BY a field, but applying some sorting "before" the grouping (meaning - group by game, but get me the first game from each group of identical games), I think you can use an inner select:

SELECT m.america,
       m.america_un,
       m.game,
       m.genre,
       m.id,
       m.platform,
       m.url
FROM `main-games` m
JOIN (SELECT game,
       min(id) AS id
       FROM `main-games`
       ORDER BY tally_rank
       GROUP BY game) as t
ON m.game = t.game AND m.id = t.id
ORDER BY m.tally_rank
LIMIT 3

The inner table has the game name and the minimum ID for each game - it tells which one is the first game in each group. The outer select uses this ID to find this specific game record and fetches the rest of the data for it.

WITHOUT DISTINCT:

 SELECT field1,field2,field3,game FROM `main-games` GROUP BY game LIMIT 3;

WITH DISTINCT:

 SELECT DISTINCT(game),field1,field2 FROM `main-games` ORDER BY tally_rank LIMIT 3;

if you cant use Distinct or Group BY, maybe try this:

SELECT america,         
america_un,         
game,         
genre,         
id,         
platform,         
url  
FROM   `main-games` M1 LEFT JOIN `main-games` M2 
ON (M1.game = M2.game AND M1.id > M2.id)
WHERE M2.id IS NULL
ORDER  BY tally_rank  LIMIT  3