1

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.

Vad.Gut
  • 521
  • 1
  • 5
  • 19
  • I have tried arranging by the field and then importing importing it to my page using php. And then parsing it in the page obiusly it was no good since i have a large db – Vad.Gut Feb 27 '13 at 10:18

4 Answers4

1

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
Community
  • 1
  • 1
Yaroslav
  • 6,476
  • 10
  • 48
  • 89
  • the query looks great but i need it to return the rows around a certain row for example. while searching for "sfdgd" it should return 6 4gfhfrgfr 7 zxcxvfdf 8 sfdgd 9 s8545454 10 7jhgdfe45 – Vad.Gut Feb 27 '13 at 11:02
  • Yes, but as I pointed out, is a tricky scenario if you can't rely on the ID. I'm trying different solutions but seems that using `UNION` is more simple. Search on the web for `mysql range row select` to get same results as me, there are some similar questions around, but yours need to be tuned to use the "around a particular row" issue. – Yaroslav Feb 27 '13 at 11:07
1

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
palindrom
  • 18,033
  • 1
  • 21
  • 37
1

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
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • i liked the idea but i think you are mistaken with something in the code, i keep getting the error = Incorrect usage of UNION and ORDER BY – Vad.Gut Feb 27 '13 at 15:48
  • @Vadim.G . . . I forgot to put parentheses around the subqueries. They are needed when using `order by` and `limit`. – Gordon Linoff Feb 27 '13 at 16:16
  • yea i caught that one, but it dose not return the right result, second part returns perfect with the right order, but the first part returns all the results in desc instead of asc, so what i tried to do is enclose everything as a result and do a SORT BY on it but it seems to return an error. any thoughts ? – Vad.Gut Feb 27 '13 at 16:41
  • @Vadim.G . . . The results are not guaranteed in any order. An `order by` at the end does what you want, I think. An `order by` at the end of a union statement applies to the results of the union. – Gordon Linoff Feb 27 '13 at 16:42
  • #1250 - Table 'slang_categories' from one of the SELECTs cannot be used in global ORDER clause – Vad.Gut Feb 27 '13 at 16:53
  • I Got it, it should have been "order by col" , the t table can not be referred globally. GREAT JOB MY FRIEND THANKS – Vad.Gut Feb 27 '13 at 16:59
-1

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;
Minesh
  • 2,284
  • 1
  • 14
  • 22