| Max function with line item details [message #424650] |
Mon, 05 October 2009 02:33  |
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 #424685 is a reply to message #424682] |
Mon, 05 October 2009 05:56  |
cookiemonster
Messages: 13973
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
sasnrock wrote on Mon, 05 October 2009 11:47If 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 #424687 is a reply to message #424682] |
Mon, 05 October 2009 06:08 |
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; |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
