Sequence of rows

Databases
1

Hi there.

I need your help with this table:


mysql> select ID, description from tbl_description limit 10;
+----+----------------------------+
| ID | description                |
+----+----------------------------+
|  1 | OPEN-AAA FSN               |
|  2 | OPEN-AAA FSN               |
|  3 | RETURN  AAABBB             |
|  4 | REMOTE POSITIVE CH SN AAAA |
|  5 | REMOTE POSITIVE CH SN AAAA |
|  6 | REMOTE POSITIVE CH SN AAAA |
|  7 | OPEN-AAA FSN               |
|  8 | REMOTE POSITIVE CH SN AAAA |
|  9 | OPEN-AAA FSN               |
| 10 | REMOTE POSITIVE CH SN AAAA |
+----+----------------------------+
10 rows in set

I need select with query in MySQL only the rows 2, 3 and 4 because I have this sequence of rows repeated and alternate throughout the table:


+----+----------------------------+
| ID | description                |
+----+----------------------------+
|  2 | OPEN-AAA FSN               |
|  3 | RETURN  AAABBB             |
|  4 | REMOTE POSITIVE CH SN AAAA |

All the rest of rows does not interest me…
Can you help me?
Thank you for any help.

2

you would have to explain why those specific rows are chosen and not other ones. Why does 2, 3, 4 meet your criteria but 3, 7, 10 does not meet it for example.

3 
SELECT id
     , description 
  FROM tbl_description 
[COLOR="#0000FF"] WHERE id IN ( 2, 3, 4 )[/COLOR]
4

Hello, this is easy my friend … :wink:
I don’t need to seek help for this, do you agree? :slight_smile:

I need to search between the rows in this positive string:

  1. OPEN-AAA FSN — open string,
  2. RETURN aaabbb
  3. REMOTE POSITIVE CH SN YYYY — close string

Thanks-

5

sorry, i don’t understand

6

there is no order in a database table unless you use an order by clause. So how do you determine the order of the table? By ID?

So are you suggesting you want those three rows because they appear in a particular order without a repeat of those values?
what if id 16,17,18 ALSO contained that pattern, would you also want those rows and their ids returned?

7

Thnk you for reply and answers.

Yes Sir, order by ID: if the sequence exist is order by ID.

So are you suggesting you want those three rows because they appear in a particular order without a repeat of those values?

Yes Sir.

what if id 16,17,18 ALSO contained that pattern, would you also want those rows and their ids returned?

I need extract the rows with id 16,17,18 and id 2,3,4.

8

I’d say do it in your scripting language (PHP?)

9

Thanks for reply: I believe don’t have other solution …

10

OK, a little messy, and probably subject to (heavy) improvement, but here’s a quick suggestion:


select *
from test d
where d.id in 
(
(
select a.id
from test a
inner join test b
on a.id = b.id-1
inner join test c
on a.id = c.id-2
where (a.description = 'OPEN-AAA FSN' and b.description = 'RETURN  AAABBB')
 and  (a.description = 'OPEN-AAA FSN' and c.description = 'REMOTE POSITIVE CH SN AAAA')
),
(
select a.id+1
from test a
inner join test b
on a.id = b.id-1
inner join test c
on a.id = c.id-2
where (a.description = 'OPEN-AAA FSN' and b.description = 'RETURN  AAABBB')
 and  (a.description = 'OPEN-AAA FSN' and c.description = 'REMOTE POSITIVE CH SN AAAA')
),
(
select a.id+2
from test a
inner join test b
on a.id = b.id-1
inner join test c
on a.id = c.id-2
where (a.description = 'OPEN-AAA FSN' and b.description = 'RETURN  AAABBB')
 and  (a.description = 'OPEN-AAA FSN' and c.description = 'REMOTE POSITIVE CH SN AAAA')
)
)

If you can figure out how to transpose rows to columns in MySQL (I’m not much of a MySQL guy), you can use this instead of the three SQLs:


select a.id, 
       a.id+1,
       a.id+2
from test a
inner join test b
on a.id = b.id-1
inner join test c
on a.id = c.id-2
where (a.description = 'OPEN-AAA FSN' and b.description = 'RETURN  AAABBB')
 and  (a.description = 'OPEN-AAA FSN' and c.description = 'REMOTE POSITIVE CH SN AAAA')
11

@Miguel61 ;
OK, the improvement, as expected:


select d.id,
       d.description
from test d,
(
select a.id
from test a
inner join test b
on a.id = b.id-1
inner join test c
on a.id = c.id-2
where (a.description = 'OPEN-AAA FSN' and b.description = 'RETURN  AAABBB')
 and  (a.description = 'OPEN-AAA FSN' and c.description = 'REMOTE POSITIVE CH SN AAAA')
) e
where d.id in (e.id, e.id+1, e.id+2 )

Tested only on the set of data you gave in post #1.

12

Hello itmitică
I’d appreciate your help so very much.

I try your sql queries and this WORKING !!! :slight_smile:

mysql> SELECT
	a.id,
	a.id + 1,
	a.id + 2
FROM
	tbl_c a
INNER JOIN tbl_c b ON a.id = b.id - 1
INNER JOIN tbl_c c ON a.id = c.id - 2
WHERE
	(
		a.description = 'OPEN-AAA FSN'
		AND b.description = 'RETURN  AAABBB'
	)
AND (
	a.description = 'OPEN-AAA FSN'
	AND c.description = 'REMOTE POSITIVE CH SN AAAA'
);
+----+----------+----------+
| id | a.id + 1 | a.id + 2 |
+----+----------+----------+
|  2 |        3 |        4 |
| 11 |       12 |       13 |
+----+----------+----------+
2 rows in set

My table:


SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `tbl_c`
-- ----------------------------
DROP TABLE IF EXISTS `tbl_c`;
CREATE TABLE `tbl_c` (
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of tbl_c
-- ----------------------------
INSERT INTO `tbl_c` VALUES ('1', 'OPEN-AAA FSN');
INSERT INTO `tbl_c` VALUES ('2', 'OPEN-AAA FSN');
INSERT INTO `tbl_c` VALUES ('3', 'RETURN  AAABBB');
INSERT INTO `tbl_c` VALUES ('4', 'REMOTE POSITIVE CH SN AAAA');
INSERT INTO `tbl_c` VALUES ('5', 'REMOTE POSITIVE CH SN AAAA');
INSERT INTO `tbl_c` VALUES ('6', 'REMOTE POSITIVE CH SN AAAA');
INSERT INTO `tbl_c` VALUES ('7', 'OPEN-AAA FSN');
INSERT INTO `tbl_c` VALUES ('8', 'REMOTE POSITIVE CH SN AAAA');
INSERT INTO `tbl_c` VALUES ('9', 'OPEN-AAA FSN');
INSERT INTO `tbl_c` VALUES ('10', 'REMOTE POSITIVE CH SN AAAA');
INSERT INTO `tbl_c` VALUES ('11', 'OPEN-AAA FSN');
INSERT INTO `tbl_c` VALUES ('12', 'RETURN  AAABBB');
INSERT INTO `tbl_c` VALUES ('13', 'REMOTE POSITIVE CH SN AAAA');
13

Miguel,

Do a

select * from tbl_c

and post the results please.

Also, use this query only:


SELECT
	d.id,
	d.description
FROM
	tbl_c d,
	(
		SELECT
			a.id
		FROM
			tbl_c a
		INNER JOIN tbl_c b ON a.id = b.id - 1
		INNER JOIN tbl_c c ON a.id = c.id - 2
		WHERE
			(
				a.description = 'OPEN-AAA FSN'
				AND b.description = 'RETURN  AAABBB'
			)
		AND (
			a.description = 'OPEN-AAA FSN'
			AND c.description = 'REMOTE POSITIVE CH SN AAAA'
		)
	) e
WHERE
	d.id IN (e.id, e.id + 1, e.id + 2);

For this table


id 	description
---------------------------
 1 | OPEN-AAA FSN
 2 | OPEN-AAA FSN
 3 | RETURN  AAABBB
 4 | REMOTE POSITIVE CH SN AAAA
 5 | REMOTE POSITIVE CH SN AAAA
 6 | REMOTE POSITIVE CH SN AAAA
 7 | OPEN-AAA FSN
 8 | REMOTE POSITIVE CH SN AAAA
 9 | OPEN-AAA FSN
10 | REMOTE POSITIVE CH SN AAAA
11 | OPEN-AAA FSN
12 | RETURN  AAABBB
13 | REMOTE POSITIVE CH SN AAAA

I get this result


id | description
------------------
 2 | OPEN-AAA FSN
 3 | RETURN  AAABBB
 4 | REMOTE POSITIVE CH SN AAAA
11 | OPEN-AAA FSN
12 | RETURN  AAABBB
13 | REMOTE POSITIVE CH SN AAAA
14

Hello itmitică
I’d appreciate your help so very much.

I try your last sql query and this WORKING !!! :slight_smile:

Thanks a lot !!!

mysql> SELECT
	d.id,
	d.description
FROM
	tbl_c d,
	(
		SELECT
			a.id
		FROM
			tbl_c a
		INNER JOIN tbl_c b ON a.id = b.id - 1
		INNER JOIN tbl_c c ON a.id = c.id - 2
		WHERE
			(
				a.description = 'OPEN-AAA FSN'
				AND b.description = 'RETURN  AAABBB'
			)
		AND (
			a.description = 'OPEN-AAA FSN'
			AND c.description = 'REMOTE POSITIVE CH SN AAAA'
		)
	) e
WHERE
	d.id IN (e.id, e.id + 1, e.id + 2);
+----+----------------------------+
| id | description                |
+----+----------------------------+
|  2 | OPEN-AAA FSN               |
|  3 | RETURN  AAABBB             |
|  4 | REMOTE POSITIVE CH SN AAAA |
| 11 | OPEN-AAA FSN               |
| 12 | RETURN  AAABBB             |
| 13 | REMOTE POSITIVE CH SN AAAA |
+----+----------------------------+
6 rows in set

My table:


SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `tbl_c`
-- ----------------------------
DROP TABLE IF EXISTS `tbl_c`;
CREATE TABLE `tbl_c` (
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of tbl_c
-- ----------------------------
INSERT INTO `tbl_c` VALUES ('1', 'OPEN-AAA FSN');
INSERT INTO `tbl_c` VALUES ('2', 'OPEN-AAA FSN');
INSERT INTO `tbl_c` VALUES ('3', 'RETURN  AAABBB');
INSERT INTO `tbl_c` VALUES ('4', 'REMOTE POSITIVE CH SN AAAA');
INSERT INTO `tbl_c` VALUES ('5', 'REMOTE POSITIVE CH SN AAAA');
INSERT INTO `tbl_c` VALUES ('6', 'REMOTE POSITIVE CH SN AAAA');
INSERT INTO `tbl_c` VALUES ('7', 'OPEN-AAA FSN');
INSERT INTO `tbl_c` VALUES ('8', 'REMOTE POSITIVE CH SN AAAA');
INSERT INTO `tbl_c` VALUES ('9', 'OPEN-AAA FSN');
INSERT INTO `tbl_c` VALUES ('10', 'REMOTE POSITIVE CH SN AAAA');
INSERT INTO `tbl_c` VALUES ('11', 'OPEN-AAA FSN');
INSERT INTO `tbl_c` VALUES ('12', 'RETURN  AAABBB');
INSERT INTO `tbl_c` VALUES ('13', 'REMOTE POSITIVE CH SN AAAA');
16

[QUOTE=itmitică;5161783]@Miguel61 ;
OK, the improvement, as expected:


select d.id,
       d.description
from test d,
(
select a.id
from test a
inner join test b
on a.id = b.id-1
inner join test c
on a.id = c.id-2
where (a.description = 'OPEN-AAA FSN' and b.description = 'RETURN  AAABBB')
 and  (a.description = 'OPEN-AAA FSN' and c.description = 'REMOTE POSITIVE CH SN AAAA')
) e
where d.id in (e.id, e.id+1, e.id+2 )

Tested only on the set of data you gave in post #1.[/QUOTE]

One problem with this solution is it depends on there being no gaps in the id numbers.

17

True.

@Miguel61 ;
This could be a problem: gaps in the id numbers.


+----+----------------------------+
| ID | description                |
+----+----------------------------+
|  1 | OPEN-AAA FSN               |
|  3 | OPEN-AAA FSN               |
|  5 | RETURN  AAABBB             |
|  6 | REMOTE POSITIVE CH SN AAAA |
| 10 | REMOTE POSITIVE CH SN AAAA |
| 11 | REMOTE POSITIVE CH SN AAAA |
| 14 | OPEN-AAA FSN               |
| 16 | REMOTE POSITIVE CH SN AAAA |
| 20 | OPEN-AAA FSN               |
| 21 | REMOTE POSITIVE CH SN AAAA |
+----+----------------------------+

I’m thinking it’s solvable by using the successive system rowids or rownums instead of id or together with id. I’m not sure if MySQL has something like that or the proper mechanisms to induce the row succession: nextrow+1, nextrow+2.

But the OP requested that id be the one in use. I’m not sure how you could test for these gaps. It should test for id discontinuation between these couple values (OPEN-AAA FSN, RETURN AAABBB), (RETURN AAABBB, REMOTE POSITIVE CH SN AAAA).

One way to do that is to find the set of couple records (OPEN-AAA FSN, REMOTE POSITIVE CH SN AAAA) having but one other used id between them, consecutive or discontinued, and then filter out those couples where that row between them is different from RETURN AAABBB.

I’ll give it more thought tomorrow.

18

… and this is what I came up with. Still a little rough on the edges.

@Miguel61 ;

PROBLEM
We have consecutive (1,2,3) triple records scattered in the table.
Find them.

STEPS

  1. Find and narrow down consecutive (1,3) couple records

To narrow down the record set for this couple of records, we use this info:

  • the id for (1) is always smaller then the id for (3) by at least 2 units:
    a.id <= b.id - 2

  • we combine two sets of such records:
    those with max values for (1) with those with min values for (3)

  1. Eliminate unfitted (1,3) couple records

The approach is to eliminate:

  • those that don’t have a (2) record between them:
    f.description = ‘RETURN AAABBB’ and (f.id > e.maxa and f.id < e.maxb)

  • those that have one other record between them:
    and not exists (select g.id from test g where g.id != f.id and (g.id > e.maxa and g.id < e.maxb))

CODE

Test table


create table test (id int, description varchar(50));

INSERT INTO test VALUES (1, 'OPEN-AAA FSN');
INSERT INTO test VALUES (3, 'OPEN-AAA FSN');
INSERT INTO test VALUES (4, 'RETURN AAABBB');
INSERT INTO test VALUES (5, 'RETURN AAABBB');
INSERT INTO test VALUES (6, 'REMOTE POSITIVE CH SN AAAA');
INSERT INTO test VALUES (10, 'REMOTE POSITIVE CH SN AAAA');
INSERT INTO test VALUES (11, 'REMOTE POSITIVE CH SN AAAA');
INSERT INTO test VALUES (14, 'OPEN-AAA FSN');
INSERT INTO test VALUES (16, 'REMOTE POSITIVE CH SN AAAA');
INSERT INTO test VALUES (20, 'OPEN-AAA FSN');
INSERT INTO test VALUES (21, 'REMOTE POSITIVE CH SN AAAA');
INSERT INTO test VALUES (22, 'OPEN-AAA FSN');
INSERT INTO test VALUES (34, 'RETURN AAABBB');
INSERT INTO test VALUES (86, 'REMOTE POSITIVE CH SN AAAA');
INSERT INTO test VALUES (93, 'OPEN-AAA FSN');
INSERT INTO test VALUES (102, 'REMOTE POSITIVE CH SN AAAA');
INSERT INTO test VALUES (225, 'RETURN AAABBB');
INSERT INTO test VALUES (452, 'REMOTE POSITIVE CH SN AAAA');
INSERT INTO test VALUES (800, 'RETURN AAABBB');

SQL Query


select e.maxa,
       f.id,
       e.maxb
from
(
select c.maxa,
       c.maxb
from
(
select max(a.id) maxa,
       b.id maxb
from 
(
select id
from test
where description = 'OPEN-AAA FSN'
) 
a,
(
select id
from test
where description = 'REMOTE POSITIVE CH SN AAAA'
) 
b
where a.id <= b.id - 2
group by b.id
) c
inner join 
(
select a.id mina,
       min(b.id) minb
from 
(
select id
from test
where description = 'OPEN-AAA FSN'
) 
a,
(
select id
from test
where description = 'REMOTE POSITIVE CH SN AAAA'
) 
b
where a.id <= b.id - 2
group by a.id
order by a.id asc
) d
on c.maxa = d.mina and c.maxb = d.minb
) e,
test f
where f.description = 'RETURN AAABBB'
and (f.id > e.maxa and f.id < e.maxb)
and not exists (select g.id from test g where g.id != f.id and (g.id > e.maxa and g.id < e.maxb))
19

Thank you for help. Excellent work!