CODEWITHSUNDEEP
mysqlsql
Yuri Aniceto
Yuri Aniceto

Reputation: 1

Only return one value for name, SQL Query

I'm trying to find a SQL query that returns stuff in this order

(Those limits on 99781 and 99780 key it's already by the system, doesn't have to limit on show)

I was using:

SELECT t2.name as name, t1.key as keyy, t1.value as valuee 
FROM player_storage t1 INNER JOIN players t2 on t2.id = t1.player_id 
WHERE t1.value > 0 AND t1.key IN (99781,99780,99777) 
ORDER BY keyy+0 DESC, valuee+0 DESC LIMIT 5

Output:

name keyy valuee
Max 99781 1
Carl 99780 1
Max 99777 402
Carl 99777 395
Paul 99777 370

But if name Max have 99781 as 1, and 99777 key as 400. I don't want to return his 99777 key, since he already have 99781 key. Same with Carl (99780 key).

The result I want:

name keyy valuee
Max 99781 1
Carl 99780 1
Paul 99777 370
Mike 99777 320
John 99777 55

A user here on Stack Overflow, helped me get to this:

SELECT t2.name as name, t1.key as keyy, t1.value as valuee 
FROM player_storage t1 INNER JOIN players t2 on t2.id = t1.player_id 
WHERE t1.value > 0 
AND
(
    t1.key IN (99781, 99780)
    OR
    (
        t1.key IN (99777)
        AND NOT EXISTS
        (
            SELECT 5 FROM player_storage tx
            WHERE tx.player_id = t1.player_id
            AND tx.key > t1.key
        )
    )
)
ORDER BY keyy+0 DESC, valuee+0 DESC LIMIT 5

But it's getting only 1 name with 99777 key, even if there are more to show. It's not going to limit 5, result of this:

name keyy valuee
Max 99781 1
Carl 99780 1
Paul 99777 370

Upvotes: 0

Views: 1015

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

You seem to want one row per name, with the rows ordered by key in descending order. That would be:

select ps.*
from (select p.name as name, ps.key as key, p.value as value
             row_number() over (partition by name order by key desc) as seqnum
      from player_storage ps join
           players p
           on p.id = ps.player_id 
      where ps.key in (99781, 99780, 99777)
     ) ps
where seqnum = 1;

All your values are positive numbers, so I don't understand why there is a condition on positive values. Of course, you can add and value > 0 to the subquery to enforce this.

Upvotes: 1

Related Questions