3 Replies Latest reply: Apr 2, 2012 4:36 PM by tem RSS

    return first row entered based on date column

    tem
      I'm trying to select the first entered row in a table, as judged by the datetime column. If more than one row has the same date and time, then only one row should be returned (any row having that datetime is fine). Some processing will occur on that row and then it will be deleted. The select statement is used thereafter to select the next (first) entered row in the table, etc. This way, the rows are processed first-in first-out (FIFO) style. Here's my example table:

      create table my_table
      (
      datetime date,
      firstname varchar2(50)
      )

      insert into my_table(datetime, firstname) values(to_date('2012-04-02 11:00:00', 'YYYY-MM-DD HH24:MI:SS'),'ken');

      insert into my_table(datetime, firstname) values(to_date('2012-04-02 11:00:00', 'YYYY-MM-DD HH24:MI:SS'),'john');

      insert into my_table(datetime, firstname) values(to_date('2012-04-02 11:00:00', 'YYYY-MM-DD HH24:MI:SS'),'sue');

      commit;

      Here's my example select statement, which returns simply one row of the above, since all are the same date and time:

      SELECT *
      FROM my_table
      WHERE datetime = ( select min(datetime) from my_table )
      AND rownum = 1;

      My question is, if I use the following

      SELECT *
      FROM my_table
      WHERE datetime = ( select min(datetime) from my_table );

      It returns all 3 rows:

      DATETIME FIRSTNAME
      02-APR-12 11:00:00 ken
      02-APR-12 11:00:00 john
      02-APR-12 11:00:00 sue

      So, wouldn't setting rownum = 2 return john, and rownum = 3 return sue? For example,

      SELECT *
      FROM my_table
      WHERE datetime = ( select min(datetime) from my_table )
      AND rownum = 2;

      return no rows. I just want to make sure I'm understanding how the select statement above works. It seems to work fine for returning one row having the minimum date and time. If this is always the case, then everything is fine. But I wouldn't have expected it not to return one of the other rows when rownum is 2 or 3, which makes me question why? Maybe I can learn something here. Any comments much appreciated.

      Edited by: tem on Apr 2, 2012 2:06 PM
        • 1. Re: return first row entered based on date column
          Frank Kulash
          Hi,
          tem wrote:
          ... So, wouldn't setting rownum = 2 return john, and rownum = 3 return sue? For example,, ROWNUM

          SELECT *
          FROM my_table
          WHERE datetime = ( select min(datetime) from my_table )
          AND rownum = 2;

          return no rows. I just want to make sure I'm understanding how the select statement above works. It seems to work fine for returning one row having the minimum date and time. If this is always the case, then everything is fine. But I wouldn't have expected it not to return one of the other rows when rownum is 2 or 3, which makes me question why? Maybe I can learn something here. Any comments much appreciated.
          ROWNUM is assigned as rows are fetched and considered for inclusion in the result set. If the row is not chosen for any reason, the same ROWNUM will be reused with the next row fetched. ROWNUM=2 will not be assigned until a row with ROWNUM=1 has been included in hte result set.
          So, in your example:
          SELECT  * 
          FROM    my_table
          WHERE   datetime = ( select min(datetime) from my_table )
          AND     rownum = 2;
          Say the first row that happens to be fetched has firstname='ken'. It is assigned ROWNUM=1, and fails the WHERE clause condition "WHERE rownum = 2".
          Say the next row fetched has firstname='john'. ROWNUM=1 hasn't been used yet, so this row is also assigned ROWNUM=1, and it fails the WHERE clause for the same reason. Likewise with the next row; it also is assigned ROWNUM=1, and it also fails.

          When using ROWNUM in a WHERE clause, you almost always want to say "ROWNUM = 1" or "ROWNUM <= n".

          You could also use the analytic ROW_NUMBER function:
          WITH     got_r_num     AS 
          (
               SELECT     datetime, firstname
               ,     ROW_NUMBER () OVER (ORDER BY  datetime)     AS r_num
               FROM     my_table
          )
          SELECT     datetime, firstname
          FROM     got_r_num
          WHERE     r_num     = 1
          ;
          Here, all values of r_num are available, so it would make sense to say things like "WHERE r_num = 2" or "WHERE r_num >= 2".

          Edited by: Frank Kulash on Apr 2, 2012 5:31 PM
          Added to explanation.
          • 2. Re: return first row entered based on date column
            rp0428
            >
            So, wouldn't setting rownum = 2 return john, and rownum = 3 return sue?
            >
            No - see ROWNUM Pseudocolumn in the SQL Language doc
            http://docs.oracle.com/cd/B28359_01/server.111/b28286/pseudocolumns009.htm
            >
            Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:

            SELECT * FROM employees
            WHERE ROWNUM > 1;
            The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.
            • 3. Re: return first row entered based on date column
              tem
              Thanks, makes sense now. I'm adding this related link for those interested.

              http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:912210644860