Select next 6 Rows if col1="searchterm"

Question

MY TABLE (the lines which starts with "AA" contains company code and name only. the next 4 lines below that 1st like contains that company details and after that 4 lines again a line with company name... and so on)

id  col1           col2                      col4 | col5 | col6  | col7 | col8
 1  AAV987        Alcatop Pvt                     |      |       |      |
 2  126B          Burger                       10 |  --  |  15   |  20  |   56 
 3  957A          Pizza                        17 |  19  |  44   |  29  |   97
 4  45A5          Mushroom                     59 |  22  |  15   |  --  |   --
 5  8999          Fish                         27 |  39  |  --   |  20  |   20  
 1  AA5697        Nomendus Curies
 2  126B          Burger                       42 |  26  |  25   |  29  |   60 
 3  957A          Pizza                        65 |  65  |  90   |  29  |    9
 4  45A5          Mushroom                     15 |  97  |  --   |  64  |   65
 5  8999          Fish                          7 |  19  |  15   |  --  |   20

I NEED THIS

i need a query which helps me to do like this

if search term is "AA5697" then it should get me the data as below

id  col1           col2                      col4 | col5 | col6  | col7 | col8
 1  AA5697        Nomendus Curies
 2  126B          Burger                       42 |  26  |  25   |  29  |   60 
 3  957A          Pizza                        65 |  65  |  90   |  29  |    9
 4  45A5          Mushroom                     15 |  97  |  --   |  64  |   65
 5  8999          Fish                          7 |  19  |  15   |  --  |   20

similar question 1: Mysql Select Next & Prev row not order by id

similar question 2: sql pulling a row for next or previous row of a current row

i looked at them. but it confuse me and the query in 1st link didn't wokred perfectly :(

You shouldn't think the records are ordered. You should use two tables instead of that one. — gkovacs90, Jun 10 '13 at 09:32
Agree, this database design really is crap, sorry, no offense. Read about database normalization... — fancyPants, Jun 10 '13 at 09:33
Make Parent-child relationship between two tables. What's the perpose of putting all recoreds inside one single table? — 7alhashmi, Jun 10 '13 at 09:33
@gkovacs90 thanks for the comment. yes it is the main problem. they are not ordered. but is an example data only, i have a huge data in same form — user2457175, Jun 10 '13 at 09:33
@user2457175 But you're explicitly saying you assume the rows to have some kind of order. So there needs to be some kind of field to order the records by. — Thorsten Dittmar, Jun 10 '13 at 09:35

gkovacs90 · Accepted Answer · 2013-06-11T08:09:11.963

If the provided columns are all the columns, there's no way to solve this correctly. In a relational database a table is an unordered set of rows. Of course, there is a physical order on the HDD, but logically You cann't presume there is any order of them. There's no "next" row.

You can try to solve this with various queries, altering the table, etc... You can be lucky, but there's no proofable working solution.

This is a bad database model. There's no even proof the values are correct. Ask the one who sent this for you to send a real database. A parent-child connection between the companies and the data rows would make this problem easier.

But: If there is a hidden ID field with increasing integers(in the sample code there isn't), You can use it to define an order in the queries. With that, You can solve the problem easily by checking how far are the IDs of the value-records from the selected company's ID.

Note: there's no proof, that You can add a correct ID column if there isn't one. If there is, instead of queries, You should use it for define 2 other tables and fill them with the data.

EDIT:

Unaccept an answer and editing a post is not a good practice. You provided 2 links. At the first one, the records are ordered by name, at the second one they are ordered by date.

Your new ID field is almost good, but if You have N records, ID should run from 1 to N.

i am sorry, but i think it is possible to do like that, please have a look on the similar question links which i have added in bottom in question.. please! — user2457175, Jun 11 '13 at 08:00
@user2457175 Erm, no, gkovacs90 is absolutely correct. The database design is crap, the data is crap, whatever you try based on this design will be crap. Sorry to say that, but truth hurts sometimes. — fancyPants, Jun 11 '13 at 08:08

score 0 · Answer 2 · answered Jun 10 '13 at 09:36

0

make your question a bit clear please. (The example is confusing.)

from what I have gathered it should be

 SELECT col1, col2, col3, col4, col5, col6, col7, col8 from MY_TABLE LIMIT 7 ORDER BY col1;

answered Jun 10 '13 at 09:36

Sunny Patel

525
2
5
13

thanks for the reply. it don't work :( is it possible to create a new column and copy the companycode to next 6 rows. – user2457175 Jun 10 '13 at 09:43
I'm really confused as to what you are trying to do here. So you should clarify the question making it easy for anyone else who might have a similar problem. – Sunny Patel Jun 10 '13 at 14:18

Select next 6 Rows if col1="searchterm"

I NEED THIS

2 Answers2