Home » SQL & PL/SQL » SQL & PL/SQL » how to build a query to retrieve the records in between the dates with same field? (Oracle,10g,windows XP)
how to build a query to retrieve the records in between the dates with same field? [message #447688] Wed, 17 March 2010 06:46 Go to next message
fazal_haq2001
Messages: 35
Registered: July 2006
Location: India
Member
Hi i have a doubt in building a query.

I have a table with fields
job_no activity Date
101 anchorage 20/01/2010
102 berthing 25/01/2010
103 sailing 29/01/2010

If i want to know the status of the ship on the date '22/01/2010'
It has to show as 'anchorage', becoz on '25/01/2010' only it came to berthing from anchorage.
How to write a query to achieve this.

regards,
Haq ...
Re: how to build a query to retrieve the records in between the dates with same field? [message #447691 is a reply to message #447688] Wed, 17 March 2010 07:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:
Michel Cadot wrote on Tue, 19 January 2010 13:37
...
Post a working Test case: create table and insert statements along with the result you want with these data.
...

Why don't you do it?
In addition, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Search the first row before your date.

Regards
Michel

Re: how to build a query to retrieve the records in between the dates with same field? [message #447692 is a reply to message #447688] Wed, 17 March 2010 07:09 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Then it looks like you want to use a condition of
< TheDateYouWant
and then take the row with the latest date from the set of results.
Re: how to build a query to retrieve the records in between the dates with same field? [message #447711 is a reply to message #447688] Wed, 17 March 2010 08:06 Go to previous messageGo to next message
tejas.patel
Messages: 22
Registered: December 2008
Location: NJ
Junior Member

Please describe your question.


you want a particular date that date with date function used.

regard
tejas patel
Re: how to build a query to retrieve the records in between the dates with same field? [message #448344 is a reply to message #447711] Mon, 22 March 2010 18:52 Go to previous messageGo to next message
jagilbert
Messages: 12
Registered: March 2010
Location: Atlanta
Junior Member
The short answer:
select  activity
from    foo 
Where   "Date" = (
    select  Max("Date") 
    from    foo 
    where   "Date" <= '26-Jan-2010');


A longer answer:
I'm new to Oracle. Perhaps there less expensive way to do this. In SQL Server I would do this:
select  top 1 activity 
from    jg_foo
where   "Date" <= '01/26/2010'
order   by date asc

And in MySQL I would do this:
select  activity 
from    jg_foo
where   "Date" <= '01/26/2010'
order   by date asc 
limit 1;

But in Oracle, this doesn't work, because the rownum is evaluated before the order by clause so you never know which of the records that match the where clause will be returned.
select  activity 
from    jg_foo
where   "Date" <= '01/26/2010'
and     rownum = 1
order   by date asc 

Perhaps someone else may have some insight.

Cheers,
Jeff
Re: how to build a query to retrieve the records in between the dates with same field? [message #448346 is a reply to message #448344] Mon, 22 March 2010 19:16 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>Perhaps someone else may have some insight.

>"Date"
Should never use KEYWORD as column name.
Should not use MixedCase column name.

> '26-Jan-2010'
With Oracle characters between single quote marks are STRINGS!
'This is a string, 26-Jan-2010, not a date'
When a DATE datatype is desired, then use TO_DATE() function.
Re: how to build a query to retrieve the records in between the dates with same field? [message #448347 is a reply to message #448344] Mon, 22 March 2010 19:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In oracle, you'd do this:
select activity
from  (select  activity
              ,row_number() over (order by date_col) rnum
       from    jg_foo 
       where   date_col <= to_date('01/26/2010','dd/mm/yyyy'))
where  rnum = 1;
 


or
select  activity 
from   (select activity
        from    jg_foo
        where   date_col <= to_date('01/26/2010','dd/mm/yyyy'))
        order   by date asc)
where   rownum = 1 
Re: how to build a query to retrieve the records in between the dates with same field? [message #448355 is a reply to message #448347] Mon, 22 March 2010 20:19 Go to previous messageGo to next message
jagilbert
Messages: 12
Registered: March 2010
Location: Atlanta
Junior Member
re: the first example - Cool. I've got to get a better understanding of the OVER() clause. I wonder if it is less expensive than your second example or my subselect example, All three of which produce the desired result. I'll check it out someday...Maybe...

re: '01-jan-2010' isn't a date.
The following works as the strings are implicitly converted to dates during the insert and compare operations. I wonder if its dangerous to assume that this will work on any oracle system?

create table jg_date_test (d DATE);
insert into jg_date_test values ('1-JAN-2010');
insert into jg_date_test values ('11-JAN-2010');
insert into jg_date_test values ('1-NOV-2010');
insert into jg_date_test values ('11-NOV-2010');
select * from jg_date_test where d between '10-JAN-2010' and '01-NOV-2010';
-- produces
D        
---------
11-JAN-10
01-NOV-10


Cheers & g'Night,
Jeff

Re: how to build a query to retrieve the records in between the dates with same field? [message #448356 is a reply to message #448355] Mon, 22 March 2010 20:31 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>I wonder if its dangerous to assume that this will work on any oracle system?
Dangerous?
You make the call.

SQL> create table jg_date_test (d DATE);

Table created.

SQL> insert into jg_date_test values ('1-JAN-2010');

1 row created.

SQL> select * from jg_date_test;

D
-------------------
0001-01-20 10:00:00

SQL> insert into jg_date_test values ('2010-JAN-01');

1 row created.

SQL> select * from jg_date_test;

D
-------------------
0001-01-20 10:00:00
2010-01-01 00:00:00


which date is '09-08-07'?

[Updated on: Mon, 22 March 2010 20:55]

Report message to a moderator

Re: how to build a query to retrieve the records in between the dates with same field? [message #448401 is a reply to message #448355] Tue, 23 March 2010 03:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The Analytic solution really comes into it's own when you want to return a rows for each set of values, like this:
create table test_152 (username  varchar2(10), action_date  date);

insert into test_152 values ('User 1',sysdate);
insert into test_152 values ('User 1',sysdate+5);
insert into test_152 values ('User 1',sysdate+10);
insert into test_152 values ('User 1',sysdate-10);

insert into test_152 values ('User 2',sysdate-1);
insert into test_152 values ('User 2',sysdate+4);
insert into test_152 values ('User 2',sysdate+8);
insert into test_152 values ('User 2',sysdate-8);

select username,action_date
from  (select username
             ,action_date
             ,row_number() over (partition by username order by action_date desc) rnum
       from   test_152)
where  rnum = 1;
Re: how to build a query to retrieve the records in between the dates with same field? [message #448411 is a reply to message #448355] Tue, 23 March 2010 04:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The following works as the strings are implicitly converted to dates during the insert and compare operations. I wonder if its dangerous to assume that this will work on any oracle system?

It will never work on mines:
SQL> create table jg_date_test (d DATE);

Table created.

SQL> insert into jg_date_test values ('1-JAN-2010');
insert into jg_date_test values ('1-JAN-2010')
                                 *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

But my case is less dangerous than BlackSwan's one.

Regards
Michel
Re: how to build a query to retrieve the records in between the dates with same field? [message #448469 is a reply to message #447688] Tue, 23 March 2010 10:54 Go to previous messageGo to next message
jagilbert
Messages: 12
Registered: March 2010
Location: Atlanta
Junior Member
@Michel: Hmm. Thats exactly what I was afraid of. Apparently there is something about my Oracle configuration that is allowing me to do this but on yours it doesn't. That tells me I should use the TO_DATE() method if I am going to create portable code. Thanks for checking that out.

@JR Analytic solution: - Cool. I've totally got to learn that.

@BlackSwan re: which date is '09-08-07'?
<sarcasm>gee i don't know... Which date is 'A week from next Christmas'.</sarcasm> Of course you have to specify the date in the correct format. Thats why I was choosing to test potentially ambiguous dates such as 1/1 and 11/1 and 1/11 and 11/11. To show that the way I am specifying my dates is working correctly and unambiguously in my case.

Cheers,
Jeff

Re: how to build a query to retrieve the records in between the dates with same field? [message #448472 is a reply to message #448469] Tue, 23 March 2010 11:12 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's always better to use an explicit date format - otherwise you have to rely on the default NLS date format of the client or server, and you've got very little control over that.

To make @BlackSwan's code work, you need to do this beforehand:
alter session set nls_date_format ='yyyy-mon-ddhh';
Previous Topic: ORA-01461: can bind a LONG value only for insert into a LONG column without using LONG values
Next Topic: How to do a conditional query
Goto Forum:
  


Current Time: Wed Dec 07 04:38:59 CST 2016

Total time taken to generate the page: 0.12278 seconds