CODEWITHSUNDEEP
mysqlsql
Tum
Tum

Reputation: 3652

Query to Select & check 1 row at a time & stop when it finds the right value (MYSQL)?

Given this table:

Col1-Col2
85 - 2
95 - 4
46 - 3
79 - 6
11 - 5
13 - 1

The requirement is that I want the system to check each row at a time, if it see col2 =3 or col2 = 1, then it will stop & it will return the between.

Ex: when user enter "85", it will look the next record "95" (col2=4 not 3 or 1) then it will look into the next record "46" (col2=3) then it will break & return the between.

Col1-Col2
95 - 4

Ex2: when user search for "46", it will show the between of 2 boundaries (46 - 3) & (13 - 1):

Col1-Col2
79 - 6
11 - 5

I think the simplest code is to use the user defined variable in mysql, something like this:

select @pv:=col1, col2 from Table1 
where @pv=85

But this code doesn't work. I searched all over internet but couldn't find similar question.

Can anyone help me? If you can modify the query in sql fiddle (http://sqlfiddle.com/#!2/24b1f/3) then it will be great.

Upvotes: 0

Views: 116

Answers (1)

Andreas Wederbrand
Andreas Wederbrand

Reputation: 40041

You really should solve that with a stored procedure and a cursor.

However, it is also possible in SQL but it gets hard to read. This is for your first example

select col1, col2 from
(select col1, 
       col2, 
       if(col2 in (1,3) and @row = 1,@row:=@row+1, @row) as end,
       if(col1 = 85 and @row = 0,@row:=1,@row) as start       
  from Table1
  join (select @row := 0 from dual) dummy) as magic
where end = 1;

http://sqlfiddle.com/#!2/24b1f/23

Upvotes: 2

Related Questions