Today I am facing a challenge for me, that I could solve with multiple queries, a little bit of PHP and some other funny things, but I was wondering whether what I mean to do can be achieved with a single query and/or stored fn/procedure.
I explain myself better: in a list of cities, I need to pick up a value (say "general expenses") of that named city (say "Rome").
Pretty simple.
What I would like to do is: Have 6 records for the same value BEFORE and 6 AFTER the Rome one. So I would see something:
| position | city | expenses |
| 35 | Paris | 1364775 |
| 36 | Milan | 1378499 |
| 37 | New York | 1385759 |
| 38 | London | 1398594 |
| 39 | Oslo | 1404648 |
| 40 | Munchen | 1414857 |
| 41 | Rome | 1425773 | *** <--this is the value I need
| 42 | Dublin | 1437588 |
| 43 | Athen | 1447758 |
| 44 | Stockholm | 1458593 |
| 46 | Helsinki | 1467489 |
| 47 | Moscow | 1477484 |
| 48 | Kiev | 1485665 |
These values will populate a bars chart.
As you can see there is also another complexity level: the position.
Position must be calculated on all the records.
So let's say I have 100 records, I will have the ranking position from 1 to 100, but only the "limited 13" records must be output.
Any link, suggestion, tutorial or else the could help me out with that? Thank you in advance as always.
EDIT Position MUST BE calculated. It is not an input value.
Anyway, thanks folks for all your efforts.
SELECT
all_ranked.*
FROM (select rank
from (SELECT a.id AS id2,
@curRow := @curRow + 1 AS Rank
FROM the_table a
JOIN
(SELECT @curRow := 0) r
ORDER BY position DESC
) AS B)
where B.id=1234567) as rank_record, <--- just one record - value of rank
(SELECT a.id AS id2,
@curRow := @curRow + 1 AS Rank
FROM the_table a
JOIN
(SELECT @curRow := 0) r
ORDER BY position DESC
) AS all_ranked <--- all ranked users
where all_ranked.rank>=rank_record.rank-6 and all_ranked.rank>=rank_record.rank+6
Create 2 queries joined in one. The first gets position and the second sets positions and cut's desired fragment
You could use a stored function/procedure that takes in an input that indicates the subject record, e.g., "Rome" to derive a ranking, which I shall refer to here as the perceived ID (PID) for that record, e.g., 41. You can then use a variable @PID to store that location.
Then you can do your ranking query again but select all records.
SELECT .... WHERE Ranking BETWEEN (@PID-6) AND (@PID+6)
An advantage to doing it this way is that the function/procedure can take in an additional parameter to allow it to fetch X records after and Y records before that ranking. It would be easier to read and maintain as well.
Performing it as a single query without the use of PHP would be tricky as you need to insert a WHERE clause in which the condition is the result of another query.
If your position is going to be continuous unique number you can use sub-query in where condition.
SELECT `position`, `city`, `expenses`
FROM table_name
WHERE `position` > (
SELECT `position`-7
FROM table_name
WHERE `city`='Rome'
)
ORDER BY `position`
LIMIT 13
PS: I am not an expert in SQL. There may be better more efficient ways.
I haven't tried it, but this should work: You get the positions with a variable you increment while selecting in MySQL. Then you would have to select this "temporary table" twice; once to find Rome, once to find all 13 records:
select
from
(
select @rowno := @rowno + 1 as position, city, expenses
from cities
cross join (select @rowno := 0)
order by expenses
)
where abs(position -
(
select position, city
from
(
select @rowno := @rowno + 1 as position, city, expenses
from cities
cross join (select @rowno := 0)
order by expenses
)
where city = 'Rome'
)
) <= 6;
SELECT rank as position,city,expenses FROM
(SELECT @rownum := @rownum + 1 AS position, city, expenses, FIND_IN_SET( position, (
SELECT GROUP_CONCAT( position
ORDER BY position ASC )
FROM test )
) AS rank
FROM test,(SELECT @rownum := 0) r
HAVING rank BETWEEN(SELECT FIND_IN_SET( position, (
SELECT GROUP_CONCAT( position
ORDER BY position ASC )
FROM test )
)-6 AS rank
FROM test
WHERE expenses=1425773)
AND
(SELECT FIND_IN_SET( position, (
SELECT GROUP_CONCAT( position
ORDER BY position ASC )
FROM test )
)+6 AS rank
FROM test
WHERE expenses=1425773))x