?
Solved

Need to retrieve a range of records in one table

Posted on 2011-05-02
22
Medium Priority
?
589 Views
Last Modified: 2013-12-18

I need to retrieve a range of records in one table a min(timestamp) and a max(timestamp) range


SELECT a, b, count(*)
  FROM (
        SELECT t.ip a, t.signature b
          FROM org t
         WHERE t.timestamp (this needs to be the starting timestamp)
           AND t.timestamp (this needs to be the ending timestamp)
       )
 GROUP BY a, b

I know you can't use min/max to get those so don't know how to code this.
0
Comment
Question by:bkreynolds48
22 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 35506813
do you mean you want to get all records where the timestamp column is between the minimum and maximum values?

if so,  that's not really a contraint.  Every value will be between min and max.

instead, just say WHERE t.timestamp is not null

if that's not what you mean,  please elaborate.
even better,  provide sample data and expected results
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35506824
Do you mean something like this?

WHERE t.timestamp between min(timestamp) and max(timestamp)

That means, you want all the records from the table. No need to include the WHERE clause.
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 35506831
Yes I want to retrieve all records say between 1-FEB-10 and 31-MAR-10
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:sdstuber
ID: 35506842
still need a WHERE clause to exclude NULL values

unless timestamp is constrained to be not null
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35506859
>>> 1-FEB-10 and 31-MAR-10

how are those min and max?  do you mean user inputs?

if so

        SELECT t.ip a, t.signature b, count(*)
          FROM org t
         WHERE t.timestamp >= to_date('1-FEB-10','dd-MON-rr')
           AND t.timestamp <  to_date('31-MAR-10','dd-MON-rr') + 1
 GROUP BY ip, signature

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35506872
this should be ok, no need another wrapping select

  SELECT   t.ip a, t.signature b, COUNT (1)
    FROM   org t
   WHERE   t.timestamp BETWEEN starting_timestamp AND ending_timestamp
GROUP BY   ip, signature
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35506878
note  the >= on the lower bound  and <  on the upper bound with + 1

this is because

31-MAR-2010  13:00:00  > 31-MAR-2010

since a date without a visible time is really a date rounded down to 00:00:00

so, to include all of March 31,  
instead check to see if the data is less than April 1  
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35506890
>>>  BETWEEN starting_timestamp AND ending_timestamp


This has the problem of dropping the end day as noted above

however,  if your timestamp will always have all values truncated to midnight (00:00:00)  then between will work

or as with the previous post,  using <=  but not adding 1

t.timestamp <=  to_date('31-MAR-10','dd-MON-rr')
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 35506918

sdstuber..........
how are those min and max?  do you mean user inputs?

if so

        SELECT t.ip a, t.signature b, count(*)
          FROM org t
         WHERE t.timestamp >= to_date('1-FEB-10','dd-MON-rr')
           AND t.timestamp <  to_date('31-MAR-10','dd-MON-rr') + 1
 GROUP BY ip, signature



These are not exactly user inputs - this script is a part of a perl script that I am trying to adjust to do this.
The perl script is meant to pull one weeks worth of data from this table and create a pdf report that get shoved into the database.  I have not been able to get any query to work inside this perl script to pull out the needed data as I am not very familiar with perl.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35506934
"user"  -real user, or "user" a script, either way.

they are inputs.  they are not values derived from the data , right?


0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 35507043
Correct - they are inputs

SELECT ip , signature , count(*)
             FROM event_266
            WHERE timestamp >= to_date('1-FEB-10','DD-MON-YY')
             AND timestamp <  to_date('31-MAR-10','DD-MON-YY') + 1
    GROUP BY ip, signature;

returns no data even though there is data to be retrieved.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35507116
are you sure there is data?

what query did you use to verify?

also,  is timestamp  a "date" or "timestamp" type? or is it a string?

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35507152
just curious,  did you intend to use 2010 data? or 2011?

also  YY or (RR) invites reinventing the Y2K bug  
I recommend using 4 digits years and YYYY format
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 35507187
timestamp is a date field

I have verified that there is data from January 2010 through April 14 2010
Just changed the query to use the YYYY but still no data returned.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 35507229
if your final query looks like this...


SELECT ip , signature , count(*)
             FROM event_266
 WHERE timestamp >= to_date('1-FEB-2010','DD-MON-YYYY')
             AND timestamp <  to_date('31-MAR-2010','DD-MON-YYYY') + 1
    GROUP BY ip, signature;


Then there's something else happening.

Try running that query via sql*plus.  If it returns data then the problem is something happening in your perl script and you're not really running the query you think you are.

If the query doesn't return data, then your verification is checking something other than what this query is looking at because it's pretty simple.

What "could" be wrong with it?





 
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 35507252
If I take out......... the second line I get back 2422 record............in sqlplus but none if I leave the second line in

SELECT ip , signature , count(*)
             FROM event_266
 WHERE timestamp >= to_date('1-FEB-2010','DD-MON-YYYY')
                 GROUP BY ip, signature;


0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35507275
what does this return?

SELECT to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') ts
             FROM event_266
 WHERE timestamp >= to_date('1-FEB-2010','DD-MON-YYYY')
and rownum < 20
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35507285
oops, try this instead, sorry


select * from (
SELECT to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') ts
             FROM event_266
 WHERE timestamp >= to_date('1-FEB-2010','DD-MON-YYYY')
order by ts)
and rownum < 20
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 35507309
That returns 19 rows
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35507384
what is the output?
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 35689129
SELECT to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') ts
             FROM event_266
 WHERE timestamp >= to_date('1-FEB-2010','DD-MON-YYYY')
   and rownum < 20   3    4  ;

TS
-------------------
2011-03-19 13:00:26
2011-03-19 13:39:58
2011-03-19 13:39:58
2011-03-19 13:40:01
2011-03-19 13:46:03
2011-03-19 13:58:11
2011-03-19 14:13:01
2011-03-19 14:13:05
2011-03-19 14:31:23
2011-03-19 15:11:10
2011-03-19 15:11:10
2011-03-19 15:13:24
2011-03-19 15:17:03
2011-03-19 16:10:06
2011-03-19 16:11:45
2011-03-19 17:02:57
2011-03-19 17:02:57
2011-03-19 18:30:54
2011-03-19 18:30:54

19 rows selected.


SELECT to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') ts
             FROM event_266
     WHERE timestamp >= to_date('1-FEB-2010','DD-MON-YYYY')
    order by ts)
and rownum < 20   4    5    6  ;
and rownum < 20
*
ERROR at line 6:
ORA-00933: SQL command not properly ended

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35689270
you left off part of the 2nd query

try it again, with the whole thing.

based on the results of the first query you have 2011 data, but your date range above is for 2010 as noted in http:#35507152

the 2nd query will determine absolutely if that's the problem or not
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses
Course of the Month7 days, 23 hours left to enroll

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question