CODEWITHSUNDEEP
mysql
Vladimir Szabo
Vladimir Szabo

Reputation: 458

Returning 2 rows above and 2 rows below desired ID

I found some answers for this question on stack but non of them worked. I can say I understand mysql fairly well but not well enough to make this work.

Here it goes, I have table that looks like this:

id | Name | Sales
---|------|------
0  | Tim  | 9
1  | Jim  | 5
2  | Joe  | 3
3  | Ryan | 6
4  | Phil | 7
5  | James| 1

Notice the ID-s may not be in order in the future, so it may be 1, 4, 10, 11, 12 etc, still unique but not incremental like +1 due to the user deleting some stuff and adding other stuff.

Now I want to select and ID (for example 3) and get the two rows above it and bellow it. If the ID is 0 then select 4 rows bellow 0 (so always 5 rows).

UPDATE oh my, I am so dumb. I don`t need to select two rows up and down by the ID, I need to select them by the sales instead. But still need the rows up and down from a specific ID bud depending from the sales. Sorry everyone. I still need help though.

Upvotes: 2

Views: 280

Answers (3)

Jonny 5
Jonny 5

Reputation: 12389

Let's say you want to pull out 5 ids, that are close to id 12. You could calculate the distance to the desired id by subtraction and use the absolute value for ordering.

SELECT ABS(12-10) => 2
SELECT ABS(12-11) => 1
SELECT ABS(12-12) => 0
SELECT ABS(12-13) => 1
SELECT ABS(12-14) => 2

E.g. a query to pull 5 rows with ordering ids by distance to the desired id 12, could look like:

SELECT * FROM `test_table` ORDER BY ABS(12-CAST(`id` AS SIGNED)) LIMIT 5

NOTE: This technique will become slower according to amount of ids, as all ids will be sorted. Tested it with 100 000 rows at debian, Intel Xeon 2.4Ghz, 4gb ram, speed for the query was ~0.11 sec.

To keep performance up on larger tables, use a WHERE clause to limit the number of rows to be sorted:

WHERE id BETWEEN (desired id - 1000) AND (desired id + 1000)

Upvotes: 1

Maciej Los
Maciej Los

Reputation: 8591

Please, read the documentation:
SELECT
UNION

Here is an example:

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

Change it to your needs ;)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

If you want to do this by id order, it is a bit tricky, because of the condition of always getting 4 rows. Here is an approach. The idea is to select up to 4 before the id and up to 4 after the id, along with the id itself. Then, using MySQL variables, enumerate the rows.

The final select chooses enumeration values of 0, 1, and 2. This should produce 5 rows exactly:

select t.*
from ((select t.*, @rn1 := @rn1 + 1 as rn
       from t cross join
              (select @rn1 := 0) const
       where id < MYID
       order by id desc
       limit 4
      ) union all
      (select t.*, @rn2 := @rn2 + 1 as rn
       from t cross join
              (select @rn2 := 0) const
       where id > MYID
       order by id 
       limit 4
      ) union all
      (select t.*, 0 as rn
       from t
       where id = MYID
      )
     ) t
where rn <= 2;

Upvotes: 1

Related Questions