| Help with this query [message #284748] |
Fri, 30 November 2007 16:13  |
bztom33
Messages: 95
Registered: June 2005
|
Member |
|
|
Hi,
How would I write a query to achieve the following output?
for each date, get only the latest one.
output:
11/28/2007 13:32
11/29/2007 07:57
create table test2(
run_date date not null);
insert into test2 (run_date) values('11/28/2007 13:00');
insert into test2 (run_date) values('11/28/2007 13:00');
insert into test2 (run_date) values('11/28/2007 13:32');
insert into test2 (run_date) values('11/29/2007 07:00');
insert into test2 (run_date) values('11/29/2007 07:57');
Thanks
Tom
|
|
|
|
|
|
| Re: Help with this query [message #284755 is a reply to message #284750] |
Fri, 30 November 2007 17:18  |
bztom33
Messages: 95
Registered: June 2005
|
Member |
|
|
SELECT *
FROM (SELECT RANK() OVER(PARTITION BY RUN_DATE ORDER BY RUN_DATE DESC) rn,
RUN_DATE
FROM test2 RUN_DATE in (SELECT unique(max(RUN_date)) FROM test2));
only return the a single max run_date. I want multiple distinct max run_date for each entry in the table.
[Updated on: Fri, 30 November 2007 17:22] Report message to a moderator |
|
|
|
| Re: Help with this query [message #284756 is a reply to message #284748] |
Fri, 30 November 2007 17:28 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
WOW! & WHOA! Way too complicated.
In Oracle characters between single quote marks are STRING datatypes.
'this is a string 2007-11-30 and not a date'
When you need a DATE datatype use TO_DATE function.
A good way to get what you want from SQL is by incrementally adding a column at a time.
Write SQL that generates the following results:
11/28/2007
11/29/2007
[Updated on: Fri, 30 November 2007 17:28] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Help with this query [message #284801 is a reply to message #284797] |
Sat, 01 December 2007 03:58 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Although I find that trunc(run_date) is more intuitive, and so I do agree with your solution, I fail to see why using the trunc() function on a date would be "better answer" than using a to_char().
What makes you think converting a date to a char is more costly than performing a trunc? Where did you find that one function is cheaper than the other?
|
|
|
|
| Re: Help with this query [message #284813 is a reply to message #284801] |
Sat, 01 December 2007 07:42 |
 |
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
First logically speaking, you want to work on date not on strings (representing date).
Now about performances, I don't have an Oracle database now to show you with an a test case (easy to do, see below) but how each works in short.
- to_char gets a full date (8 bytes) and a string (a pointer to n bytes), has to analyze the model string, find the first model, take the corresponding byte in the first parameter applies the transformation, add it to the result string and go on till the end of the model string
- trunc gets a full date (8 bytes), analyze the second parameter (if exists), if it is year returns the first 2 bytes, if it is month returns the first 3 bytes, if it is day (or does not exist) returns the first 4 bytes... Stop.
Test code:
declare
beg1 pls_integer;
end1 pls_integer;
beg2 pls_integer;
end2 pls_integer;
now date := sysdate;
dat date;
str varchar2(20);
begin
beg1 := dbms_utility.get_time;
for i in 1..10000 loop
str := to_char(now, 'DD-MON-YYYY');
end loop:
end1 := dbms_utility.get_time;
beg2 := dbms_utility.get_time;
for i in 1..10000 loop
dat := trunc(now);
end loop:
end2 := dbms_utility.get_time;
dbms_output.put_line('TO_CHAR: '||end1-beg1||' hsecs');
dbms_output.put_line('TRUNC : '||end2-beg2||' hsecs');
end;
/
If someone has a database...
Regards
Michel
[Updated on: Sat, 01 December 2007 10:21] Report message to a moderator |
|
|
|
|
|
| Re: Help with this query [message #284823 is a reply to message #284813] |
Sat, 01 December 2007 10:41 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Well, unfortunately, my database server does not have that kind of performace that I notice a 2 hsec difference in fetching/selecting/whatever 100,000 records.
I think this is tuning turned into obsession.
Like I said, using trunc because it is more intuitive, because you are working with dates is a valid reason. The performance/scalability is nonsense in my opinion.
(And even more so in the context of the question of the original poster. This goes way beyond the scope of the question)
[Updated on: Sat, 01 December 2007 10:42] Report message to a moderator |
|
|
|
|
|
| Re: Help with this query [message #284864 is a reply to message #284827] |
Sun, 02 December 2007 00:19 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| Michel Cadot wrote on Sat, 01 December 2007 18:30 |
And performances is not the first point I gave.
I talked about logic not performances, you talked about performances.
|
Reread those posts, and hey, you're right! I would have sworn you mentioned performance as the prime reason to do it..
Well, guess it shows how interpretation lets us see things the way we expect them to see.
Sorry for the hassle, Michel.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
