2 Replies Latest reply: Jul 3, 2013 11:57 AM by MayankSharma RSS

    query to find first and last call made by selected number for date range

    986570
      Hi,


      query to find first and last call made by selected number for date range
      according to filter:

      mobile_no : 989.....
      call_date_from : 25-april-2013
      call_date_to : 26-april-2013


      Please help
        • 1. Top-N Query
          Frank Kulash
          Hi,

          It sounds like you want a Top-N Query , something like this:
          WITH    got_nums   AS
          (
               SELECT     table_x.*     -- or list columns wanted
               ,     ROW_NUMBER () OVER (ORDER BY  call_date      ) AS a_num
               ,     ROW_NUMBER () OVER (ORDER BY  call_date  DESC) AS d_num
               FROM     table_x
               WHERE     mobile_no     = 989
               AND     call_date     >= DATE '2013-04-25'
               AND     call_date     <  DATE '2013-04-26' + 1
          )
          SELECT  *     -- or list all columns except a_num and d_num
          FROM     got_nums
          WHERE     1     IN (a_num, d_num)
          ;
          This forum is devoted to the SQL*Plus and iSQL*Plus front ends. This question doesn't have anything to do with any front end, does it? In the future, you'll get better response if you post questions like this in the PL/SQL.


           

          I hope this answers your question.
          If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and also post the results you want from that data.
          Explain, using specific examples, how you get those results from that data.
          Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
          See the SQL forum FAQ {message:id=9360002}
          • 2. Re: query to find first and last call made by selected number for date range
            MayankSharma

            As you know with Date server also save the time(which it donot show if you just run select statement  So use timestamp and rownum and ordder by clause  and make inline query to solve it.


            Even you can use &(Substitution function to make it more user friendly)