CODEWITHSUNDEEP
mysql
John Mathew
John Mathew

Reputation: 5

how to get closest record from a table on a specific condition

I have a table like below.

Name            flag
--------------------------------
ABC             success
DEF             fail
GHI             success
JKL             fail
MNO             fail
PQR             fail
STU             fail

I have to get the record next below to the "success" flag record.I have tried couple of times but i am new to mysql.

my record hhould be like :-

Name            flag
--------------------------------
JKL             fail

Upvotes: 0

Views: 35

Answers (3)

Kedar Limaye
Kedar Limaye

Reputation: 1041

1)I have considered the'id' column to consider the next record

  select * from yourtable where id = (select id from yourtable where flag='success')+1

or

2)I have also considered using rownum

CREATE TABLE yourtable
    (`Name` varchar(3), `flag` varchar(7))
;

INSERT INTO yourtable
    (`Name`, `flag`)
VALUES
    ('ABC', 'fail'),
    ('DEF', 'fail'),
    ('GHI', 'success'),
    ('JKL', 'fail'),
    ('MNO', 'fail'),
    ('PQR', 'fail'),
    ('STU', 'fail')
;

create temporary  table t1 as 

select name,flag,@rownum:=@rownum+1 as rownum
from yourtable ,(SELECT @rownum := 0) as r;

select name,flag from t1 where rownum = 
(select rownum from t1 where flag='success')+1

Check here-http://sqlfiddle.com/#!9/cbd4b/5

Upvotes: 2

Gulzaman
Gulzaman

Reputation: 429

if you are interested to use windows functions.

select *,lead(flag, success) over (order by name) as next_comm from yourtable;

Upvotes: 0

Redzix
Redzix

Reputation: 188

SELECT *
FROM [Table_1]
WHERE [Table_1].id > (SELECT [Table_1].id FROM [Table_1] WHERE flag = 'success')
LIMIT 1

Should return first row after flag "success"

Upvotes: 0

Related Questions