Home » SQL & PL/SQL » SQL & PL/SQL » Max function with line item details (10g)
Max function with line item details [message #424650] Mon, 05 October 2009 02:33 Go to next message
beetel
Messages: 96
Registered: April 2007
Member
Hi,
I have a table which has the following columns:
order_date,
order_id
I want a query that will return 3 columns: max(order_date), order_date, order_id. So if I have 3 records:

ORDER_DATE    ORDER_ID
01-SEP-2009     A
20-AUG-2009     B
12-JUL-2009     C


I want my query to return:

MAX_ORDER_DATE    ORDER_DATE     ORDER_ID
01-SEP-2009       01-SEP-2009     A
01-SEP-2009       20-AUG-2009     B
01-SEP-2009       12-JUL-2009     C


Thank you in advance.


Re: Max function with line item details [message #424652 is a reply to message #424650] Mon, 05 October 2009 02:36 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Look at MAX in its analytical form. documentation
Re: Max function with line item details [message #424656 is a reply to message #424650] Mon, 05 October 2009 03:37 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
WITH clause can also be used.

regards,
Delna
Re: Max function with line item details [message #424659 is a reply to message #424656] Mon, 05 October 2009 03:58 Go to previous messageGo to next message
sasnrock
Messages: 19
Registered: August 2007
Junior Member
SELECT ORDER_DATE,ORDER_ID,(SELECT MAX(ORDER_DATE) FROM tablename) max_ORDER_DATE FROM tablename
Re: Max function with line item details [message #424673 is a reply to message #424659] Mon, 05 October 2009 04:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, that's certainly the least efficient way of doing it.
Re: Max function with line item details [message #424682 is a reply to message #424673] Mon, 05 October 2009 05:47 Go to previous messageGo to next message
sasnrock
Messages: 19
Registered: August 2007
Junior Member
If I use with, will it be efficient than my previous query?

with
max_order_date as (select max(order_date) maxdate from tablename)
select * from tablename,max_order_date;

or any other ways ??
Re: Max function with line item details [message #424685 is a reply to message #424682] Mon, 05 October 2009 05:56 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
sasnrock wrote on Mon, 05 October 2009 11:47
If I use with, will it be efficient than my previous query?

Yes as It'll calculate the max once rather than once per row.

sasnrock wrote on Mon, 05 October 2009 11:47

or any other ways ??


pablolee wrote on Mon, 05 October 2009 08:36

Look at MAX in its analytical form. documentation
Re: Max function with line item details [message #424686 is a reply to message #424682] Mon, 05 October 2009 06:01 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Yes, that will be the most efficient way, according to my knowledge as cookiemonster sir has replied.

regards,
Delna
Re: Max function with line item details [message #424687 is a reply to message #424682] Mon, 05 October 2009 06:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The With clause won't help here - you're stil acesing the table twice.

If you go for an analytic solution, you only need to aces the table once:
create table test_077 (ORDER_DATE date,    ORDER_ID varchar2(1));

insert into test_077 values (to_date('01-SEP-2009','dd-mon-yyyy'),'A');
insert into test_077 values (to_date('20-AUG-2009','dd-mon-yyyy'),'B');
insert into test_077 values (to_date('12-JUL-2009','dd-mon-yyyy'),'C');

select order_date
      ,order_id
      ,max(order_date) over (order by null)
from   test_077;
Re: Max function with line item details [message #424688 is a reply to message #424687] Mon, 05 October 2009 06:13 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No need of "order by null", "order by" clause is optional in this case.

Regards
Michel
Previous Topic: compilation problems with function
Next Topic: Sql error: 1795 - ORA-01795: maximum number of expressions in a list is 254
Goto Forum:
  


Current Time: Sat Dec 03 01:17:22 CST 2016

Total time taken to generate the page: 0.12127 seconds