This discussion is archived
2 Replies Latest reply: Jul 3, 2013 9:57 AM by MayankSharma RSS

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

986570 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 SQL and 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 Newbie
    Currently Being Moderated

    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)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points