CODEWITHSUNDEEP
mysqlsql
nano_nano
nano_nano

Reputation: 12523

sql show partition based on found record in mysql

Sorry I know you want example code but unfortunately, I have absolutely no idea how I can implement this functionality or for what I have to search for.

Let's pretend we have this relation:

╔═══════════════════╗
║ id  name  quality ║
╠═══════════════════╣
║ 1   Will  4       ║
║ 2   Joe   9       ║
║ 3   Bill  2       ║
║ 4   Max   1       ║
║ 5   Jan   10      ║
║ 6   Susi  5       ║
║ 7   Chris 6       ║
║ 8   Noa   9       ║
║ 9   Sisi  4       ║
╚═══════════════════╝

Now I need a subset based on the data I am searching for. For instance I'm searching for the record with id 5. In my result I need more than the record of Jan, I need the two records before Jan and the two records behind Jan too. So I have the following resultset:

╔═══════════════════╗
║ id  name  quality ║
╠═══════════════════╣
║ 3   Bill  2       ║
║ 4   Max   1       ║
║ 5   Jan   10      ║
║ 6   Susi  5       ║
║ 7   Chris 6       ║
╚═══════════════════╝

Could you give me some keywords for solving this problem?

Upvotes: 2

Views: 68

Answers (3)

Deepak Rai
Deepak Rai

Reputation: 2203

SQL fiddle demo http://sqlfiddle.com/#!2/6b156a/15

(SELECT * FROM test
    WHERE id >= 5
    ORDER BY id
    LIMIT 3)

UNION 

(SELECT * FROM test
WHERE id < 5
ORDER BY id DESC
LIMIT 2)

Upvotes: 1

Lloyd Banks
Lloyd Banks

Reputation: 36659

SET @search_id = 5;

SELECT * 
FROM my_table
WHERE id BETWEEN (@search_id - 2) AND (@search_id + 2);

Upvotes: 3

Gidil
Gidil

Reputation: 4137

Assuming the ID field is always in a preset order (two before and two after the specific row can be counted via the ID field), you can do one of two simple queries:

SELECT * 
FROM   tbl1 
WHERE  id BETWEEN 3 AND 7

(An SQL Fiddle demo can be found here)

or:

SET @a:=5; 

SELECT * 
FROM   tbl1 
WHERE  id BETWEEN @a - 2 AND @a + 2

(An SQL Fiddle demo can be found here)

I hope this answer your question.
Please let me know if you need anything else.

Good luck!

Upvotes: 2

Related Questions