0

I have design an event where you register multiple fishes and I wanted a query to extract the top 3 heaviest fishes from different people. In case of tie, it should be decided by a third parameter: who registered it first. I've tested several ways I found here on stack overflow but none of them worked the way I needed.

My schema is the following:

id | playerid | playername | itemid | weight | date | received | isCurrent

Where:

id = PK, AUTO_INCREMENT - it's basically an index
playerid = the unique code of the person who registered the fish
playername = name of the person who registered the fish
itemid = the code of the fish
weight = the weight of the fish
date = pre-defined as CURRENT_TIMESTAMP, the exact time the fish was registered
received = pre-defined as 0, it really don't matter for this analysis
isCurrent = pre-defined as 1, basically every time this event runs it updates this field to 0, meaning the registers don't belong to the current version of the event.

Here you can see the data I'm testing with

my problem is: How to avoid counting the same playerid for this rank more than once?

Query 1:

SELECT `playerid`, `playername`, `itemid`, `weight` 
FROM `event_fishing` 
WHERE `isCurrent` = 1 AND `weight` IN (    
    SELECT * FROM
        (SELECT MAX(`weight`) as `fishWeight` 
        FROM `event_fishing` 
        WHERE `isCurrent` = 1 
        GROUP BY `playerid`
        LIMIT 3) as t) 
ORDER BY `weight` DESC, `date` ASC 
LIMIT 3

Query 2:

SELECT * FROM `event_fishing`
INNER JOIN 
(SELECT playerid, MAX(`weight`) as `fishWeight` 
FROM `event_fishing` 
WHERE `isCurrent` = 1 
GROUP BY `playerid`
LIMIT 3) as t
ON t.playerid = `event_fishing`.playerid AND t.fishWeight = `event_fishing`.weight
WHERE `isCurrent` = 1
ORDER BY weight DESC, date ASC
LIMIT 3

Keep in mind that I must return at least the fields: playerid, playername, itemid, weight, that the version of the event must be the actual (isCurrent = 1), one playerid per line with the heaviest weight he registered for this version of the event and the date is registered.

Expected output for the data I've sent:

    id |playerid|playername|itemid|weight|      date      |received| isCurrent
     7 |  3734  |Mago Xxx  | 7963 | 1850 | 2018-07-26 00:17:41 | 0 | 1
    14 |   228  |Night Wolf| 7963 | 1750 | 2018-07-26 19:45:49 | 0 | 1
     8 |  3646  |Test Spell| 7159 | 1690 | 2018-07-26 01:16:51 | 0 | 1

Output I'm getting (with both queries):

playerid|playername|itemid|weight
  3734  |Mago Xxx  | 7963 | 1850
   228  |Night Wolf| 7963 | 1750 
   228  |Night Wolf| 7963 | 1750 

Thank you for the attention.

EDIT: I've followed How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL? since my query is very similar to the accepted answer, in the comments I've found something that at a first glance seem to have solved my problem but I've found a case where the accepted answer fail. Check http://sqlfiddle.com/#!9/72aeef/1

If you take a look at data you'll notice that the id 14 was the first input of 1750 and therefore should be second place, but the MAX(id) returns the last input of the same playerid and therefore give us a wrong result.

Despite the problems seems alike, mine has a greater complexity and therefore the queries that were suggested doesn't work

EDIT 2:

I've managed to solve my problem with the following query: http://sqlfiddle.com/#!9/d711c7/6

But I'll leave this question open because of two things: 1- I don't know if there's a case where this query might fail 2- Despite we limit a lot the first query, I still think this can be more optimized, so I'll leave it open to any one that might know a better way to solve the issue.

  • Possible duplicate of [How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?](https://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql) – Alex Jul 27 '18 at 01:46
  • not a duplicate, add an edit. – Anderson Faria Jul 27 '18 at 02:44
  • Oh, it's a duplicate alright – Strawberry Jul 27 '18 at 06:41
  • didn't you read my first edit? It's not duplicated, my problem is totally different, and that's exact why using the same query didn't worked out. Read my problem description again and check my query in the second fiddle. – Anderson Faria Jul 27 '18 at 11:25

0 Answers0