SitePoint Sponsor

User Tag List

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

    Exclude all the rows with the same lID that are within an hour

    Hi guys, I need suggestion for show from my mysql table only the first row grouped by unique tblID and to exclude all the rows with the same tblID that are within an hour compared to the previous row.

    E.g.:
    PHP Code:
    +----------+---------------------+
    tblID    theDate             |
    +----------+---------------------+
    77002221 2013-06-12 11:17:29 |
    56009055 2013-06-12 11:17:29 |
    77002221 2013-06-12 11:47:29 |
    77002221 2013-06-12 12:17:29 |
    77002221 2013-06-12 12:47:29 |
    +----------+---------------------+ 
    In this example I need show only this output:
    PHP Code:
    +----------+---------------------+
    tblID    theDate             |
    +----------+---------------------+
    77002221 2013-06-12 11:17:29 |
    56009055 2013-06-12 11:17:29 |
    77002221 2013-06-12 12:47:29 |
    +----------+---------------------+ 
    Because this rows compared to the first row with tblID 77002221 fall within one hour:
    PHP Code:
    +----------+---------------------+
    tblID    theDate             |
    +----------+---------------------+
    77002221 2013-06-12 11:47:29 |
    77002221 2013-06-12 12:17:29 |
    +----------+---------------------+ 
    Can you help me?
    Thank you in advance.

  2. #2
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    hello, I tried this query union but the output is incorrect:
    PHP Code:
    mysqlSELECT
        tblID
    ,
        
    theDate
    FROM
        
    `timediff`
    WHERE
        tblID NOT IN 
    (
            
    SELECT
                tblID
            FROM
                
    `timediff`
            
    WHERE
                
    (
                    
    theDate >= ADDTIME(theDate'1:01:01')
                )
        )
    UNION ALL
        SELECT
            tblID
    ,
            
    theDate
        FROM
            
    `timediff`;
    +----------+---------------------+
    tblID    theDate             |
    +----------+---------------------+
    77002221 2013-06-12 11:17:29 |
    56009055 2013-06-12 11:17:29 |
    77002221 2013-06-12 11:47:29 |
    77002221 2013-06-12 12:17:29 |
    77002221 2013-06-12 12:47:29 |
    77002221 2013-06-12 11:17:29 |
    56009055 2013-06-12 11:17:29 |
    77002221 2013-06-12 11:47:29 |
    77002221 2013-06-12 12:17:29 |
    77002221 2013-06-12 12:47:29 |
    +----------+---------------------+
    10 rows in set 
    Instead of:
    PHP Code:
    +----------+---------------------+ 
    tblID    theDate             
    +----------+---------------------+ 
    77002221 2013-06-12 11:17:29 
    56009055 2013-06-12 11:17:29 
    77002221 2013-06-12 12:47:29 
    +----------+---------------------+ 
    Code:
    DROP TABLE IF EXISTS `timediff`;
    CREATE TABLE `timediff` (
      `tblID` int(10) DEFAULT NULL,
      `theDate` datetime DEFAULT NULL,
      `id` int(10) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of timediff
    -- ----------------------------
    INSERT INTO `timediff` VALUES ('77002221', '2013-06-12 11:17:29', '1');
    INSERT INTO `timediff` VALUES ('56009055', '2013-06-12 11:17:29', '2');
    INSERT INTO `timediff` VALUES ('77002221', '2013-06-12 11:47:29', '3');
    INSERT INTO `timediff` VALUES ('77002221', '2013-06-12 12:17:29', '4');
    INSERT INTO `timediff` VALUES ('77002221', '2013-06-12 12:47:29', '5');

  3. #3
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Resolved:
    1. Create a second table timediff1;
    2. Insert the min data value in table timediff1 for tblID;
    3. Join from table timediff and timediff1 and check the timediff.

    PHP Code:
    mysqlDROP TABLE
    IF EXISTS `timediff1`;

    CREATE TABLE `timediff1` (
        `
    tblIDINT (10) DEFAULT NULL,
        `
    theDatedatetime DEFAULT NULL,
        `
    idINT (10NOT NULL AUTO_INCREMENT,
        
    PRIMARY KEY (`id`)
    ENGINE MyISAM;

    INSERT INTO `timediff1` (tblIDtheDate)(
        
    SELECT
            tblID
    ,
            
    MIN(thedate)
        
    FROM
            timediff
        GROUP BY
            tblID
    );

    SELECT
        a
    .*, b.*, TIMEDIFF(a.thedateb.thedate) AS diff
    FROM
        
    `timediffa
    JOIN 
    `timediff1b ON a.tblID b.tblID
    WHERE
        
    (
            
    TIMEDIFF(a.thedateb.thedate) = '00:00:00'
            
    OR TIMEDIFF(a.thedateb.thedate) >= '01:01:01'
        
    );
    Query OK0 rows affected

    Query OK
    0 rows affected

    Query OK
    2 rows affected
    Records
    2  Duplicates0  Warnings0

    +----------+---------------------+----+----------+---------------------+----+----------+
    tblID    theDate             id tblID    theDate             id diff     |
    +----------+---------------------+----+----------+---------------------+----+----------+
    77002221 2013-06-12 11:17:29 |  77002221 2013-06-12 11:17:29 |  00:00:00 |
    56009055 2013-06-12 11:17:29 |  56009055 2013-06-12 11:17:29 |  00:00:00 |
    77002221 2013-06-12 12:47:29 |  77002221 2013-06-12 11:17:29 |  01:30:00 |
    +----------+---------------------+----+----------+---------------------+----+----------+
    3 rows in set 
    Or
    PHP Code:
    mysqlSELECT
        b
    .*
    FROM
        
    (
            
    SELECT
                min
    (theDateCTIME,
                
    addtime(min(theDate), '01:00:00'CTIME_1
            FROM
                timediff
        
    a,
        
    timediff b
    WHERE
        b
    .theDate a.CTIME
    OR b.theDate a.CTIME_1;
    +----------+---------------------+----+
    tblID    theDate             id |
    +----------+---------------------+----+
    77002221 2013-06-12 11:17:29 |  |
    56009055 2013-06-12 11:17:29 |  |
    77002221 2013-06-12 12:47:29 |  |
    +----------+---------------------+----+
    3 rows in set 


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
  •