CODEWITHSUNDEEP
mysql
TheBook
TheBook

Reputation: 1708

Get the order of the name in the column

I am trying to get the stop_name DEFwhen the following order ABC, DEF, GHI of the stop_name exists in behaviourtable where the behaviour_id does not increment by one and the name exist several time in the table also it does not always exist once. How can I get it with mysql query?

Simple:

1  Tree
3  Tree     
5  ABC
8  ABC
9  DEF
11 DEF
13 DEF
17 GHI
21 Moon

Table:

CREATE TABLE IF NOT EXISTS behaviour(
    behaviour_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    mac VARCHAR(30) NOT NULL,
    stop_name VARCHAR(30) NOT NULL,    
    stop_distance INT(11) NOT NULL, 
    speed INT(11) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)

This query works when the id increment by one

SELECT
    b3.behaviour_id
FROM
    behaviour b1
    INNER JOIN behaviour b2 ON b1.behaviour_id = b2.behaviour_id - 1
    INNER JOIN behaviour b3 ON b2.behaviour_id = b3.behaviour_id - 1
WHERE
    CONCAT(b1.stop_name, b2.stop_name, b3.stop_name) = CONCAT('ABC', 'DEF', 'GHI')
    AND b1.mac = '12:EF:34:DF:89:JH';

Upvotes: 0

Views: 38

Answers (1)

Carsten Massmann
Carsten Massmann

Reputation: 28196

Something like

select b1.id i1,b2.id i2,b3.id i3 FROM behav b1 
INNER JOIN behav b2 ON b2.name='DEF' AND b2.id>b1.id
INNER JOIN behav b3 ON b3.name='GHI' AND b3.id>b2.id
WHERE b1.name='ABC'

should work and give the following result:

i1  i2  i3
5   9   17
8   9   17
5   11  17
8   11  17
5   13  17
8   13  17

Of course, if you only want the first occurence, do

select b1.id i1,b2.id i2,b3.id i3 FROM behav b1 
INNER JOIN behav b2 ON b2.name='DEF' AND b2.id>b1.id
INNER JOIN behav b3 ON b3.name='GHI' AND b3.id>b2.id
WHERE b1.name='ABC' ORDER BY i1,i2 LIMIT 1

Upvotes: 1

Related Questions