-1

i have a table with a column of numbers

35
24
7
8
11
35
9
11
6
6
23
23
34
5
14
31
26
2
16
0
6
23
23

i want to check if the last 3 records

6
23
23

exist in previous records but with the same order and get as result the next number.

in my example : 

6
23
23

exist with same order and result must be : 34

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
user1385619
  • 9
  • 3
  • 1
    Do you have some other column that determines the order? – jpw Apr 01 '15 at 01:11
  • yes i have another column id (total 2 columns id and number) – user1385619 Apr 01 '15 at 01:22
  • Did you have a chance to try my answer? – jpw Apr 01 '15 at 22:59
  • yes, but i did something wrong i thing, i am new in mysql queries... i am cum-fused with the function, my table name is vista and has 2 columns id (auto integer) and numbers (varchar(2)) what i have to change because i take this error : Error Code: 1146. Table 'test.t' doesn't exist – user1385619 Apr 03 '15 at 00:45
  • I've updated my answer to reflect that the table is named vista and the columns id and number, please give it a try now :) – jpw Apr 03 '15 at 09:43
  • i get : Error Code: 1054. Unknown column 't1.number' in 'field list' – user1385619 Apr 03 '15 at 13:20
  • So change number to numbers if that is what your column is called. I've updated my answer again. – jpw Apr 03 '15 at 13:26
  • looks like works now!!! i do not know about if the results is ok, but the query run and no error!! thanks a million for your patience:) – user1385619 Apr 03 '15 at 13:47
  • Works fine the results are correct!!!!! Can i ask you something more please, what i should do in this case : Error Code: 1242. Subquery returns more than 1 row, i want to to view all the rows ? – user1385619 Apr 03 '15 at 14:16
  • I'm sorry but I don't understand. In any case is probably better to post a new question so more people can see it and possibly help. – jpw Apr 03 '15 at 14:18

1 Answers1

0

Assuming that your numbers are ordered by an auto_incremented column named id then this query should work:

select numbers 
from vista 
where id = (
    select b.id + 3 from (
      select t1.id, t1.numbers t1val, t2.numbers t2val, t3.numbers t3val 
      from vista t1 
      join vista t2 on t1.id = t2.id-1 
      join vista t3 on t1.id = t3.id-2 
      where t1.id = (select max(id) - 2 from vista)
    ) a
    join (
      select t1.id, t1.numbers t1val, t2.numbers t2val, t3.numbers t3val 
      from vista t1 
      join vista t2 on t1.id = t2.id-1 
      join vista t3 on t1.id = t3.id-2 
      where t1.id < (select max(id) - 2 from vista)
    ) b 
  on a.t1val = b.t1val 
  and a.t2val = b.t2val 
  and a.t3val = b.t3val 
  and a.id <> b.id
) 
order by id limit 1;

If the id column doesn't contain a sequence you can generate one with an appropriate row numbering query.

Sample SQL Fiddle

jpw
  • 44,361
  • 6
  • 66
  • 86