SitePoint Sponsor

User Tag List

Results 1 to 20 of 20
  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Sequence of rows

    Hi there.

    I need your help with this table:
    Code:
    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:
    Code:
    +----+----------------------------+
    | 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. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    I need select with query in MySQL only the rows 2, 3 and 4
    Code:
    SELECT id
         , description 
      FROM tbl_description 
     WHERE id IN ( 2, 3, 4 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT id
         , description 
      FROM tbl_description 
     WHERE id IN ( 2, 3, 4 )
    Hello, this is easy my friend ...
    I don't need to seek help for this, do you agree?

    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. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    I need to search between the rows in this positive string:
    sorry, i don't understand
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #7
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Thnk you for reply and answers.
    Quote Originally Posted by guelphdad View Post
    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?
    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. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    I'd say do it in your scripting language (PHP?)

  9. #9
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    I'd say do it in your scripting language (PHP?)
    Thanks for reply: I believe don't have other solution ...

  10. #10
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    OK, a little messy, and probably subject to (heavy) improvement, but here's a quick suggestion:

    Code:
    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:
    Code:
    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. #11
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    @Miguel61 ;
    OK, the improvement, as expected:

    Code:
    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. #12
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Hello itmitică
    I'd appreciate your help so very much.

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

    Code:
    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:
    Code:
    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. #13
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Miguel,

    Do a
    Code:
    select * from tbl_c
    and post the results please.

    Also, use this query only:

    Code:
    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
    Code:
    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
    Code:
    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. #14
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Hello itmitică
    I'd appreciate your help so very much.

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

    Thanks a lot !!!!!

    Code:
    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:
    Code:
    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');

  15. #15
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    OK then.

    You're welcome.

  16. #16
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by itmitică View Post
    @Miguel61 ;
    OK, the improvement, as expected:

    Code:
    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.
    One problem with this solution is it depends on there being no gaps in the id numbers.

  17. #17
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    True.

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

    Code:
    +----+----------------------------+
    | 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. #18
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    ... 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)


    2. 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
    Code:
    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
    Code:
    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. #19
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Thank you for help. Excellent work!

  20. #20
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    No problem.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •