Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2012
    Posts
    32

    Red face Unanswered: how to pick the first row of each condition specified in select query ?

    The below query works fine However, in some cases i get 2 rows returned for each condition for example Hour = 1 Minute <=5 returns 2 row. I want to pick the first row only for each hour How to do that ?

    select SQLDB_MAX,SQLDB_ASSIGNED,(SQLDB_ASSIGNED + SQLDB_POOLED),SQLDB_TIMESTAMP As SUMOFAP from IMDashDB.PRODUCTION_SQLDB_TOTALS WHERE HOUR(SQLDB_TIMESTAMP) IN(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19 ,20,21,22,23) AND MINUTE(SQLDB_TIMESTAMP) <= 5 ;



    Code:
    CREATE TABLE IMDashDB.PRODUCTION_SQLDB_TOTALS (
    
      SQLDB_POOLED INT,
      SQLDB_MAX  INT,
      SQLDB_ASSIGNED INT,
    
      SQLDB_TIMESTAMP TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP  
      ) ;

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Which rows would you pick for the following example:
    Code:
    CREATE TABLE IMDashDB.PRODUCTION_SQLDB_TOTALS (
       SQLDB_POOLED   INT
    ,  QLDB_MAX       INT
    ,  QLDB_ASSIGNED  INT
    ,  QLDB_TIMESTAMP TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP  
       ) ;
    
    INSERT INTO IMDashDB.PRODUCTION_SQLDB_TOTALS VALUES
       ( 1,  2,  3, Date('2010-02-02 00:10'))
    ,  ( 5,  7,  9, Date('2010-02-02 00:10'))
    ,  ( 4,  5,  6, Date('2010-02-02 00:10'))
    ,  (21, 22, 23, Date('2010-02-02 01:10'))
    ,  (25, 27, 29, Date('2010-02-02 01:10'))
    ,  (24, 25, 26, Date('2010-02-02 01:20'))
    ,  (41, 42, 43, Date('2010-02-02 02:10'))
    ,  (45, 47, 49, Date('2010-02-02 02:20'))
    ,  (44, 45, 46, Date('2010-02-02 02:20'));
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2012
    Posts
    32
    The first row for each hour that got inserted.

    1, 2, 3, Date('2010-02-02 00:10'
    21, 22, 23, Date('2010-02-02 01:10'
    41, 42, 43, Date('2010-02-02 02:10

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    try this:
    Code:
    select *
    from (
    select 
      rownumber() over (partition by hour(SQLDB_TIMESTAMP) order by SQLDB_TIMESTAMP) rn_
    , t.*
    from IMDashDB.PRODUCTION_SQLDB_TOTALS t
    where hour(SQLDB_TIMESTAMP) between 1 and 23 and minute(SQLDB_TIMESTAMP)<=5
    )
    where rn_=1
    Note that for the following timestamps you will get only the 1-st row according to you query.
    2014-01-01-01.01.00
    2014-01-02-01.01.00
    Is this correct?

    If not, then you should use the following 'partition by' clause instead:
    partition by date(SQLDB_TIMESTAMP), hour(SQLDB_TIMESTAMP)
    Regards,
    Mark.

Tags for this Thread

Posting Permissions

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