CODEWITHSUNDEEP
mysqlsql
Vad.Gut
Vad.Gut

Reputation: 531

Mysql get a number of before and afer rows

I am having a hard time creating a MySql query that will search a table, row by row and after that return 5 of the surrounding rows on each side.

The problem is that the rows are arranged alphabetically and we can't use the id field

Basically the point is to get 11 rows with the current one exactly in the middle while they are arranged alphabetically.

Upvotes: 1

Views: 189

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

This is really easy with union. Try this:

(select t.* from t where t.col <= YOURNAME
 order by t.col desc
 limit 6
)
union all
(select t.* from t where t.col > YOURNAME
 order by t.col
 limit 5
)
order by t.col

The first part of the query returns the five before. The second returns the five after.

By the way, if you have duplicates, you might want this instead:

(select t.* from t where t.col = YOURNAME)
union all
(select t.* from t where t.col < YOURNAME
 order by t.col desc
 limit 5
)
union all
(select t.* from t where t.col > YOURNAME
 order by t.col
 limit 5
)
order by t.col

Upvotes: 1

palindrom
palindrom

Reputation: 19101

You can solve it with a temp table if you dont have too many records in your table:

create temporary table temp_yourtable(
    id int auto_increment,
    ....,
    primary key(id)
)
select ... from yourtable;

select t.* from temp_yourtable t, temp_yourtable t1
    where t1.thealpabeticcolumn = your_key and t.id between t1.id - 5 and t1.id + 5

Upvotes: 1

Yaroslav
Yaroslav

Reputation: 6534

So you want to select a range of rows, row_number should be your friend as you say you can't rely on the id. Check this widely accepted and detailed SO answer about the use of mySQL ROW_NUMBER.

Then try this working SQL Fiddle code. But I'm still tuning it a bit as it does recover the amount of rows you need by setting up the values on the WHERE tt.row_number between 3 and 7 line but those lines are not around a row selected by you somehow. Tricky thing.

Test data:

id    col1
1    adfg
2    sg5r
3    34tdfgdf
4    ergdfd
5    ghjghghj
6    4gfhfrgfr
7    zxcxvfdf
8    sfdgd
9    s8545454
10    7jhgdfe45
11    fbvmso
12    sdfg9dj3
13    zjvjude89
14    _sdfdi3

The query:

SELECT Table1.* 
  FROM Table1
 WHERE col1 IN (
              SELECT col1
                FROM (SELECT t.col1, 
                             @curRow := @curRow + 1 AS row_number
                        FROM Table1 t
                        JOIN (SELECT @curRow := 0) r
                      ) tt
               WHERE tt.row_number between 3 and 7
             )
ORDER BY col1;

Result data:

ID  COL1
3   34tdfgdf
6   4gfhfrgfr
4   ergdfd
5   ghjghghj
7   zxcxvfdf

Upvotes: 1

Minesh
Minesh

Reputation: 2302

Try like below, based on your parameters:

SELECT * FROM table WHERE id < 5 LIMIT 10
UNION
SELECT * FROM table WHERE id = 5
UNION
SELECT * FROM table WHERE id > 5 LIMIT 10;

Upvotes: -1

Related Questions