Home » SQL & PL/SQL » SQL & PL/SQL » Getting max value without using group by (merged 3)
Getting max value without using group by (merged 3) [message #404130] Wed, 20 May 2009 09:10 Go to next message
pkirangi
Messages: 74
Registered: August 2005
Member
I have a querry which is something like this:

select
A.order_id
A.YYYY
B.XXXX
B.YYYY
C.XXXX
C.YYYY
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
from
    A,
    B,
    C,
    D,
    E,
    F,
    G
where
       .
AND    .
AND    .
AND    .
AND    .


which yeilds a single row per order.

Table Y has the following set of rows
order_id           status         status_date
1234                booked       2-may-2009 13:42:21
1234                booked       2-may-2009 14:42:21
1234                booked       2-may-2009 12:42:21
2341                booked       3-may-2009 13:42:21
2341                booked       4-may-2009 14:42:21
2341                booked       5-may-2009 12:42:21


Now I also need to link the above query with table Y with the following conditions

     A.order_id = y.order_id
and y.status='booked'


to get the status date from table y.


When I link it with table Y as you can see I would get duplicate rows.
I need to get the max of status date.
I dont want to use group by and do a max. Is ther any other way out?

Thanks
PHK

Re: Getting max value without using group by (merged 3) [message #404133 is a reply to message #404130] Wed, 20 May 2009 09:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
When I link it with table Y as you can see I would get duplicate rows.

I don't see any duplicate, all rows are different.

Quote:
I dont want to use group by and do a max.

Have a look at ROW_NUMBER, RANK and DENSE_RANK functions.

Regards
Michel
Re: Getting max value without using group by [message #404137 is a reply to message #404133] Wed, 20 May 2009 09:24 Go to previous messageGo to next message
pkirangi
Messages: 74
Registered: August 2005
Member
Thanks Michel for your quick response.
What I meant was when you link the above mentioned query with table Y using conditions

A.order_id = y.order_id
and y.status='booked'

This would result in all rows from the main query getting duplicated.

[Updated on: Wed, 20 May 2009 09:26]

Report message to a moderator

Re: Getting max value without using group by [message #404140 is a reply to message #404137] Wed, 20 May 2009 09:31 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Instead of selecting directly from y, create an inline view that selects order_id and max of status date where status = booking and join onto that. You will still need to use group by, but only in the inline view.
Re: Getting max value without using group by [message #404141 is a reply to message #404140] Wed, 20 May 2009 09:40 Go to previous messageGo to next message
pkirangi
Messages: 74
Registered: August 2005
Member
Thanks pablolee.
Yes That is one solution. But using an inline view, will that not effect the performance of the main query?

PHK

[Updated on: Wed, 20 May 2009 09:40]

Report message to a moderator

Re: Getting max value without using group by [message #404142 is a reply to message #404141] Wed, 20 May 2009 09:52 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
./fa/1659/0/
Yes, No, Maybe. Only you have the data, only you can tell.
Re: Getting max value without using group by [message #404150 is a reply to message #404141] Wed, 20 May 2009 10:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Think about what you're asking - at some point in the processing, you need to look at all the rows in y for a specific order and get the one with the most recent date.

Now, depending on what percentage of the table Y you're going to be looking at, there are different approaches to this, but every single one of them will involve looking at more data and doing more work than your query does at the moment, but details like that are among the many many things that we need to know to give a proper answer, and that you don't tell us.

Why don't you want to use the obvious solution?
Re: Getting max value without using group by [message #404185 is a reply to message #404150] Wed, 20 May 2009 12:51 Go to previous messageGo to next message
pkirangi
Messages: 74
Registered: August 2005
Member
I guess I was just looking for alternatives.
Re: Getting max value without using group by [message #404204 is a reply to message #404185] Wed, 20 May 2009 16:03 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Looking for an alternative to the obvious, without trying the obvious seems silly at best. Your choice I guess
Re: Getting max value without using group by (merged 3) [message #404228 is a reply to message #404130] Thu, 21 May 2009 00:56 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
you can use partition by clause
select order_id,max(status_date) over (partition by order_id)
from table
Re: Getting max value without using group by (merged 3) [message #404247 is a reply to message #404228] Thu, 21 May 2009 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which does not return ONLY the row with the max date but all rows with a date that is not its own.

Regards
Michel
Re: Getting max value without using group by (merged 3) [message #404361 is a reply to message #404247] Thu, 21 May 2009 08:46 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Hi Michel I mean this
SQL> create table tal
  2  (
  3  a varchar(1),
  4  b number
  5  );

Table created.
SQL> insert into tal values('a',1);

1 row created.

SQL> insert into tal values('a',2);

1 row created.

SQL>  insert into tal values('b',56);

1 row created.

SQL>  insert into tal values('b',65);

1 row created.

SQL> select distinct a,max(b) over (partition by a) from tal;

A MAX(B)OVER(PARTITIONBYA)
- ------------------------
a                        2
b                       65


Re: Getting max value without using group by (merged 3) [message #404363 is a reply to message #404361] Thu, 21 May 2009 08:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you use an analytical function AND distinct, you can be quite sure you wrongly use the analytical function.
And then it is the case here.

Regards
Michel

[Updated on: Thu, 21 May 2009 08:53]

Report message to a moderator

Re: Getting max value without using group by (merged 3) [message #404370 is a reply to message #404130] Thu, 21 May 2009 09:13 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Then make it as scalar subquery. Something like this.
select
A.order_id
A.YYYY
B.XXXX
B.YYYY
C.XXXX
C.YYYY
.
.
.
.
.
.
.
.
.
.

.
.
.
.
.
(select max(status_date) from y where order_id = a.order_id) status_date
from
    A,
    B,
    C,
    D,
    E,
    F,
    G
where
       .
AND    .
AND    .
AND    .
AND    .


Regards

Raj
Re: Getting max value without using group by (merged 3) [message #404373 is a reply to message #404370] Thu, 21 May 2009 09:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which will give you the same results as with analytical function.

Regards
Michel
Re: Getting max value without using group by (merged 3) [message #404552 is a reply to message #404373] Fri, 22 May 2009 04:34 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Agreed and I believe that's what the OP wants. I think this is lot more neater than the distinct + analytic combination.

Regards

Raj
Previous Topic: PLS_INTEGER Vs NUMBER data type
Next Topic: Procedure -Performance Issue..
Goto Forum:
  


Current Time: Fri Dec 09 12:02:01 CST 2016

Total time taken to generate the page: 0.10207 seconds