0

I have a table set up as follows:

killerId | killId 
1           | 4
1           | 4
2           | 1 
3           | 4
4           | 2
5           | 2
1           | 6

This essentially is a killfeed that shows who killed who for a game I am working on.

I want to be able to generate a query that will allow me to give players the ability to see who has killed them the most.

The response ideally would be something like:

killerId | mostKilledId | timesKilled
1        | 4            | 2
2        | 1            | 1
3        | 4            | 1
4        | 2            | 2
5        | 2            | 2

For my use case, I need to see a list of all players and who their "nemesis" is (the person who has killed them the most).

I have:

SELECT DISTINCT killerId, killedId, COUNT(*) FROM kills GROUP BY killerId, killedId;

Which gets me the following response:

killerId | killedId | count
1        | 4        | 2
2        | 1        | 1
3        | 4        | 1
4        | 2        | 1
5        | 2        | 1
1        | 5        | 1

I do not want to see duplicate killerId's here and would instead prefer to ONLY show the the killerId and killedId that has the highest count. In the example above, I don't want to see the final kill because it is not higher than the 2 kills on id 4.

EDIT: Per answers in the comments, I've got:

SELECT a.killerId, a.killedId, MAX(a.count) 
FROM (SELECT killerId, killedId, COUNT(*) as count FROM kills GROUP BY killerId, killedId) AS a 
INNER JOIN(SELECT killerId, killedId, MAX(count) count 
FROM (SELECT killerId, killedId, COUNT(*) as count FROM kills GROUP BY killerId, killedId) AS c 
GROUP BY c.killerId, c.killedId) AS b 
ON a.killerId = b.killerId AND a.killedId = b.killedId
GROUP BY a.killerId, a.killedId;

However I am still getting the same results as earlier and I am not feeling as if I am understanding what is happening here/what the right path forward is.

Steve
  • 226
  • 3
  • 12
  • 1
    There's no need for `DISTINCT` when you use `GROUP BY`, since the grouping makes it distinct. – Barmar Jul 14 '19 at 03:32
  • Are you using MySQL 8? This is easier to do with window functions. – Barmar Jul 14 '19 at 03:33
  • See https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1. Your query will be the table that you want to get the rows with the max value of the `count` column from. – Barmar Jul 14 '19 at 03:35
  • @Barmar I'm either not understanding it correctly or that post does not answer my question. I need to know how many times a row exists with a killerId and killedId and which instance of killerId/killedId pairing is occurring the most for each killedId. It looks like the post you sent works if your second column is not an id but a count of some sort? – Steve Jul 14 '19 at 03:51
  • Your query correctly finds out how many times a row exists with a killerid and killedid. So you just want to find the row with maximum count for each killer ID. The linked question explains how to find the row with the maximum value of a column in each group. The column you're maximizing can be anything. – Barmar Jul 14 '19 at 03:55
  • In the answers in that other question, replace the table name with your subquery, and use `MAX(count)` and `GROUP BY killerId` – Barmar Jul 14 '19 at 03:56
  • Please try to do this, and if you can't get it working, post what you tried and I'll reopen the question. – Barmar Jul 14 '19 at 03:57
  • Note; if you don't have a PRIMARY KEY (some way of uniquely identifying rows), then you don't really have a table – Strawberry Jul 14 '19 at 06:50
  • ```SELECT killerId, killedId, MAX(count) FROM (SELECT killerId, killedId, COUNT(*) as count FROM kills GROUP BY killerId, killedId) AS T GROUP BY killerId, killedId;``` Is what I was able to come up with but I am still messing something up :( @Barmar – Steve Jul 14 '19 at 15:04
  • ```SELECT killerId, MAX(count) FROM (SELECT killerId, killedId, COUNT(*) as count FROM kills GROUP BY killerId, killedId) AS T GROUP BY killerId;``` This gets me CLOSE to what I want, a list of requestor IDs with only their highest number of 'same person kills' but I need to see both killerId and killedId since the relationship is what I want to track. If i add the killedId back to the first SELECT and into the GROUP BY I end up back at square one again :/ – Steve Jul 14 '19 at 15:21
  • Put the code in the question, not a comment. – Barmar Jul 14 '19 at 18:57
  • Do you read the other question and understand how the solutions work? You need to join the query that uses `MAX(count)` with the query that returns all the rows. – Barmar Jul 14 '19 at 18:58
  • See [this answer](https://stackoverflow.com/a/7745635/1491895) and go down to **Joining with simple group-identifier, max-value-in-group Sub-query**. Then replace both instances of `yourTable` in the example query with `(SELECT killerId, killedId, COUNT(*) as count FROM kills GROUP BY killerId, killedId)` – Barmar Jul 14 '19 at 19:00
  • @Barmar I followed what you were saying but I don't think that I understand how the solution works in the first place. I added an edit with where I am at now to the original question. – Steve Jul 14 '19 at 20:54
  • @Barmar I've finally got it, thanks so much for your help, no need to reopen this one! – Steve Jul 14 '19 at 21:06

0 Answers0