Home » Other » Training & Certification » Help with this query
Help with this query [message #284748] Fri, 30 November 2007 16:13 Go to next message
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 #284750 is a reply to message #284748] Fri, 30 November 2007 16:16 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Here is a hint.
Use the MAX function.

P.S.
The real homework area is down near the bottom of the main page.
Re: Help with this query [message #284755 is a reply to message #284750] Fri, 30 November 2007 17:18 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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 #284759 is a reply to message #284756] Fri, 30 November 2007 18:14 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Ana, you confused me with your previous response. I must have missed something.

Call me an enabler...but here's how to return the desired output.
SELECT MAX(run_date)
FROM test2
GROUP BY TO_CHAR(run_date, 'DD-MON-YYYY');

MAX(RUN_DATE)
----------------
11/29/2007 07:57
11/28/2007 13:32
Re: Help with this query [message #284764 is a reply to message #284759] Fri, 30 November 2007 19:43 Go to previous messageGo to next message
bztom33
Messages: 95
Registered: June 2005
Member
Thanks for your quick response.

This is exactly what I am looking for.

Re: Help with this query [message #284797 is a reply to message #284759] Sat, 01 December 2007 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"group by trunc(run_date)" is a better answer.
You don't need to convert your date into a string, just operate on the date.

Regards
Michel
Re: Help with this query [message #284801 is a reply to message #284797] Sat, 01 December 2007 03:58 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
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 #284820 is a reply to message #284813] Sat, 01 December 2007 10:06 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Here's the followup:

TO_CHAR: 0 hsecs
TRUNC  : 0 hsecs


There was not a significant difference with 10,000 as the upper bound. I increased each loop to 100,000 and found 4hsecs and 2 hsecs, respectively. With an upper bound set to 1,000,000, I found 9 hsec and 4 hsecs. This was done on a fairly slow, old Dell laptop. I'm sure the results would have been different on an actual server, but there is a difference and TRUNC definitely is more scalable as the number of rows increases. Thanks for the alternative solution.
Re: Help with this query [message #284823 is a reply to message #284813] Sat, 01 December 2007 10:41 Go to previous messageGo to next message
Frank
Messages: 7880
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 #284827 is a reply to message #284823] Sat, 01 December 2007 11:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And performances is not the first point I gave.
I talked about logic not performances, you talked about performances.

But the prime directive in efficient application (efficient on any point performance, reliability, security...) is don't do anything that is not necessary.

Regards
Michel
Re: Help with this query [message #284864 is a reply to message #284827] Sun, 02 December 2007 00:19 Go to previous message
Frank
Messages: 7880
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.
Previous Topic: Somebody help me solve this
Next Topic: conflict serializable schedule
Goto Forum:
  


Current Time: Fri Dec 09 05:37:38 CST 2016

Total time taken to generate the page: 0.14666 seconds