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

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

Views: 49

Replies to This Discussion

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}

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)

RSS

© 2019   Created by Maisam Agha.   Powered by

Badges  |  Report an Issue  |  Terms of Service