CODEWITHSUNDEEP
phpmysqlsql
Marek Beseda
Marek Beseda

Reputation: 31

Select next item

i have a table like this:
table

And i want to select next row after certain playerID where state=1 and if it can't find anything AFTER that playerID start from first row

Expected result:

playerID=8 => playerID 13
playerID=15 => playerID 8

Right now i'm using sub-query and second query if result is null:

function getTargetID($killerID)
{
    $result= $this->db->fetchSingle("
        SELECT playerID FROM targets WHERE state=1 AND 
        ID>(SELECT ID FROM targets WHERE playerID=%u)
        ORDER BY state DESC,ID LIMIT 1",$killerID);
    if(!$result)
    {
        $result= $this->db->fetchSingle("
            SELECT playerID FROM targets WHERE state=1 AND 
            ID>0 ORDER BY state DESC,ID LIMIT 1");
    }
    return $result;
}

But i think this code is really ugly and not optimized

Upvotes: 0

Views: 184

Answers (4)

Clockwork-Muse
Clockwork-Muse

Reputation: 13056

Sure, this can be done in one query:

SELECT Origin.id,
       COALESCE(MIN(CASE WHEN Targets.id > Origin.id 
                         THEN Targets.playerId END), -- "after" 
                MIN(CASE WHEN Targets.id < Origin.id 
                         THEN Targets.playerId END)  -- "before"
                ) as nextPlayer
FROM Targets 
JOIN (SELECT id, playerId 
      FROM Targets
      WHERE playerId = %playerId) Origin      
  ON Origin.playerId <> Targets.playerId
WHERE state = 1
GROUP BY Origin.id

(genuine SQL Fiddle example!)
If you get a null back, then it means there wasn't a valid selection ("nobody here, pops!").

Note that your requirements are to essentially scan the entire table/index. I'm not sure I have a good way to avoid that in any db, although a window function, LAG()/MIN() and a good optimizer might help. Oh, and this version will safely deal with multiple instances of a given playerId in the table.

Other than that, what's up with using the "order of the table"? This isn't in any way guaranteed, and SQL was explicitly created to ignore such things (subverted for performance reasons, but conceptually that's the case).

EDIT:

With the requirement of next sequential playerId, here's a version I had come up with earlier:

SELECT COALESCE(MIN(CASE WHEN playerId > %playerId 
                         THEN playerId END), -- "after" 
                MIN(CASE WHEN playerId < %playerId 
                         THEN playerId END)  -- "before"
                ) as nextPlayer
FROM Targets 
WHERE state = 1

(and working fiddle)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I would suggest doing this with nested selects:

select t.*,
       coalesce((select playerID
                 from targets t2
                 where t2.state = t.state and
                       t2.id > t.id
                 order by t2.id
                 limit 1
                ),
                (select PlayerId from targets t2 where t2.state = t.state order by id limit 1)
               ) as nextPlayerId
from targets t;

You should build an index on targets(state, id, PlayerId). This index should reduce the subqueries to index lookups -- about the fastest possible way of resolving this query.

Upvotes: 1

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

SELECT 
    * 
FROM mytable 
WHERE id > (
            SELECT 
                ID 
            FROM mytable 
            WHERE player_id = 8 
)
AND state = 1
ORDER BY ID ASC
LIMIT 1

SQL Fiddle Demo

Or alternatively

SELECT
  MIN(id),
  player_id,
  state
FROM targets
WHERE state = 1
    and id > (SELECT
                ID
              FROM targets
              WHERE player_id = 8)

SQL Fiddle Demo

Upvotes: 0

JamesJ
JamesJ

Reputation: 13

SELECT playerID FROM targets
WHERE state=1 
  AND ID > (SELECT ID FROM targets 
            WHERE playerID=8)
ORDER BY ID ASC
LIMIT 1

Upvotes: 1

Related Questions