I'm trying to figure out how to write a MySQL query that will return the nearest data which Actor = 210 for in terms E_id = 3.
This is my original table:
Session Player E_id Time Actor PosX PosY index
------------------- ------ ---- ----------------------- ----- ---- ---- -----
23131885ccc560bb6c8 10125 17 01-11-2012 08:56:38.323 210 906 466 6
23131885ccc560bb6c8 10125 17 01-11-2012 08:56:38.323 210 342 540 7
23131885ccc560bb6c8 10125 17 01-11-2012 08:56:38.323 31 812 244 8
23131885ccc560bb6c8 10125 17 01-11-2012 08:56:38.323 210 248 614 9
23131885ccc560bb6c8 10125 17 01-11-2012 08:56:38.323 21 342 688 10
23131885ccc560bb6c8 10125 17 01-11-2012 08:56:38.323 49 812 170 11
23131885ccc560bb6c8 10125 17 01-11-2012 08:56:38.323 50 248 466 12
23131885ccc560bb6c8 10125 17 01-11-2012 08:56:38.323 210 718 318 13
23131885ccc560bb6c8 10125 17 01-11-2012 08:56:38.323 52 154 466 14
23131885ccc560bb6c8 10125 17 01-11-2012 08:56:38.323 211 499 250 15
23131885ccc560bb6c8 10125 3 01-11-2012 08:56:40.63 208 510 414 16
23131885ccc560bb6c8 10125 17 01-11-2012 08:56:38.323 210 248 466 17
23131885ccc560bb6c8 10125 15 01-11-2012 08:56:38.323 20 718 318 18
23131885ccc560bb6c8 10125 17 01-11-2012 08:56:38.323 52 154 466 19
23131885ccc560bb6c8 10125 17 01-11-2012 08:56:38.323 11 499 250 20
23131885ccc560bb6c8 10125 3 01-11-2012 08:56:40.63 208 510 414 21
If I fire query
select * from table where E_id = 3 or Actor = 210;
I get this result
Session Player E_id Time Actor PosX PosY index
------------------- ------ ---- ----------------------- ----- ---- ---- -----
23131885ccc560bb6c8 10125 17 01-11-2012 08:56:38.323 210 906 466 6
23131885ccc560bb6c8 10125 17 01-11-2012 08:56:38.323 210 342 540 7
23131885ccc560bb6c8 10125 17 01-11-2012 08:56:38.323 210 248 614 9
23131885ccc560bb6c8 10125 17 01-11-2012 08:56:38.323 210 718 318 13
23131885ccc560bb6c8 10125 3 01-11-2012 08:56:40.63 208 510 414 16
23131885ccc560bb6c8 10125 17 01-11-2012 08:56:38.323 210 248 466 17
23131885ccc560bb6c8 10125 3 01-11-2012 08:56:40.63 208 510 414 21
expected Result is: row with index no 13 for row index no 16 and row with index no 17 for row index no 21
Index 16 and 21 both e_id is 3
i get my ans by @eggyal example Solutution it was so simple when i get final query
Your input is E_ID, Index and Actor. Index seems to be a unique column so you don't need E_ID.
This query will find closest (by index distance). But it will only look for rows with lower index (up).
select *
from your_tbl
where 16 - `index` >= 0 and Actor = 210
order by 16 - `index`
limit 1
16 gives you 13 and 21 gives 17
and there is another query that doesn't have the limitation of looking only up. It will find the closest index:
select *
from your_tbl
where Actor = 210
order by abs(21 - `index`)
limit 1
I give two queries because I don't understand what nearest means in the question. If it's nearest in terms of index distance abs(index1 - index2) then for 16 17 is as good result as 13 since they both have Actor = 210 and they are adjacent
Here is a non-dynamic answer. That means using a union and it's not the best. (Credit to eggyal for the sqlfiddle)
Query:
select session, player, e_id,
time, actor, max(`index`)
from your_tbl
where Actor = 210
union
select session, player, e_id,
time, actor, `index`
from your_tbl
where e_id = 3
;
| SESSION | PLAYER | E_ID | TIME | ACTOR | MAX(`INDEX`) |
------------------------------------------------------------------------------------------
| 23131885ccc560bb6c8 | 10125 | 17 | January, 11 2012 08:56:38 | 210 | 17 |
| 23131885ccc560bb6c8 | 10125 | 3 | January, 11 2012 08:56:40 | 208 | 16 |
| 23131885ccc560bb6c8 | 10125 | 3 | January, 11 2012 08:56:40 | 208 | 21 |
This is the test that it works
I dont really know if the 3rd or statement is really needed but thats if your application might use it as an especial case, you can remove it as you wish. If you want to delete the extra row i used, instead of select * Just after the variable assignments, change it to:
select `Session`, `Player`, `E_id`, `Time`, `Actor`, `PosX`, `PosY`, `index` ...
I didnt include that because the [ ` ] are not always welcome in PHP
set @i = 0;
set @j = 0;
set @k = 0;
set @l = 0;
select * from (select *, @i:=@i+1 as myrow from your_tbl where E_id = 3 or Actor=210) as tb1
where myrow in
(select myrow2-1 from
(select *, @j:=@j+1 as myrow2 from your_tbl where E_id = 3 or Actor=210) as tb2
where E_id=3) or myrow in
(select myrow3+1 from
(select *, @k:=@k+1 as myrow3 from your_tbl where E_id = 3 or Actor=210) as tb3
where E_id=3) or myrow in
(select myrow4 from
(select *, @l:=@l+1 as myrow4 from your_tbl where E_id = 3 or Actor=210) as tb4
where E_id=3)