Home » SQL & PL/SQL » SQL & PL/SQL » MAX value of column (Oracle 10g)
| MAX value of column [message #603451] |
Mon, 16 December 2013 12:50  |
 |
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
|
Senior Member |
|
|
I want to find the maximum value or latest date in three different columns of the same table, i have a table with 3 dates columns where dates of operations are recorded , when the first operation was done, when the second operation was done and when third operation , i want to find out which was the latest operation per row as per last date .
create table ot_actual (op1_code varchar2(12),op1_date date,op2_code varchar2(12),op2_date date,op3_code varchar2(12),op3_date date);
insert into ot_actual (op1_code ,op1_date ,op2_code ,op2_date ,op3_code ,op3_date )
VALUES ('AM1','30-MAY-2015','RE','24-APR-2013','HO','01-JUN-2013');
insert into ot_actual (op1_code ,op1_date ,op2_code ,op2_date ,op3_code ,op3_date )
VALUES ('AM1','30-MAY-2014','IR','01-MAY-2015','HI','31-MAY-2012');
insert into ot_actual (op1_code ,op1_date ,op2_code ,op2_date ,op3_code ,op3_date )
VALUES ('AM1','30-MAY-2014','TPS','31-MAY-2014',NULL,NULL);
COMMIT;
Result i expect is mention below
operation date
AM1 '30-MAY-2015'
IR '01-MAY-2015'
TPS '31-MAY-2014'
[Updated on: Sat, 08 March 2014 13:35] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: MAX value of column [message #603453 is a reply to message #603451] |
Mon, 16 December 2013 13:39  |
 |
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
|
Senior Member |
|
|
If your answer to little foot is that you missed those rows, then this may help:
SQL> SELECT op AS operation, MAX ( dt) AS max_date
2 FROM (SELECT *
3 FROM ot_actual
4 UNPIVOT ((op, dt) FOR ( op_id, op_dt) IN ((op1_code, op1_date), (op2_code, op2_date), (op3_code, op3_date)) ))
5 GROUP BY op
6* ORDER BY op
SQL> /
OPERATION MAX_DATE
------------------------------------ -----------
AM1 30-May-2015
HI 31-May-2012
HO 01-Jun-2013
IR 01-May-2015
RE 24-Apr-2013
TPS 31-May-2014
6 rows selected.
[Updated on: Mon, 16 December 2013 13:46] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: MAX value of column [message #603455 is a reply to message #603451] |
Mon, 16 December 2013 13:49 |
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
|
Senior Member |
|
|
SQL> ed
Wrote file afiedt.buf
1 select case when op2_date>op1_date
2 then
3 case when op3_date>op2_date
4 then op3_code
5 else op2_code
6 end
7 else op1_code
8 end,
9 case when op2_date>op1_date
10 then
11 case when op3_date>op2_date
12 then op3_date
13 else op2_date
14 end
15 else op1_date
16 end
17* from ot_actual
SQL> /
CASEWHENOP2_ CASEWHENO
------------ ---------
AM1 30-MAY-15
IR 01-MAY-15
TPS 31-MAY-14
SQL>
But.... if you get more columns this gets ugly pretty fast.
|
|
|
|
|
|
|
|
| Re: MAX value of column [message #603466 is a reply to message #603455] |
Mon, 16 December 2013 14:26 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
martijn wrote on Mon, 16 December 2013 14:49
But.... if you get more columns this gets ugly pretty fast.
That's why it is better to use GREATEST:
select case greatest(
nvl(op1_date,to_date(1,'j')),
nvl(op2_date,to_date(1,'j')),
nvl(op3_date,to_date(1,'j'))
)
when op1_date then op1_code
when op2_date then op2_code
else op3_code
end operation,
greatest(
nvl(op1_date,to_date(1,'j')),
nvl(op2_date,to_date(1,'j')),
nvl(op3_date,to_date(1,'j'))
) dt
from ot_actual
/
OPERATION DT
------------ ---------
AM1 30-MAY-15
IR 01-MAY-15
TPS 31-MAY-14
SQL>
SY.
|
|
|
|
| Re: MAX value of column [message #603467 is a reply to message #603466] |
Mon, 16 December 2013 14:31 |
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
|
Senior Member |
|
|
Solomon Yakobson wrote on Mon, 16 December 2013 21:26...
That's why it is better to use GREATEST:
...
SY.
Wow..I did not know this function at all.....tnx.
|
|
|
|
|
|
|
|
|
|
| Re: MAX value of column [message #603761 is a reply to message #603744] |
Thu, 19 December 2013 03:18 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
|
That exact code is wrong, OP does not want to group across rows. It works with this specific dataset but will not work in general. Solomon and martijn gave the correct solution.
|
|
|
|
| Re: MAX value of column [message #603907 is a reply to message #603761] |
Fri, 20 December 2013 05:10 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Let's see why your code is wrong.
Let's add another row and see how your code behaves :
SQL> insert into ot_actual (op1_code ,op1_date ,op2_code ,op2_date ,op3_code ,op3_date )
2 VALUES ('AM1',to_date('30-MAY-2020','DD-MON-YYYY'),'RE',to_date('30-MAY-2015','DD-MON-YYYY'),'HO',to_date('30-MAY-2014','DD-MON-YYYY'));
1 row inserted
SQL> COMMIT;
Commit complete
For this row, we expect the output to be op1_code as "AM1" since the date is greatest.
Let's run the correct code already provided above :
SQL> select case greatest(
2 nvl(op1_date,to_date(1,'j')),
3 nvl(op2_date,to_date(1,'j')),
4 nvl(op3_date,to_date(1,'j'))
5 )
6 when op1_date then op1_code
7 when op2_date then op2_code
8 else op3_code
9 end operation,
10 greatest(
11 nvl(op1_date,to_date(1,'j')),
12 nvl(op2_date,to_date(1,'j')),
13 nvl(op3_date,to_date(1,'j'))
14 ) dt
15 from ot_actual;
OPERATION DT
------------ -----------
AM1 5/30/2015
IR 5/1/2015
TPS 5/31/2014
AM1 5/30/2020
Output is just as expected. You see that last row in the output?
Let's run your code:
SQL> select A op_code
2 ,to_char(max(B),'DD-MON-YYYY') as op_date
3 from(
4 select op1_code A,op1_date B
5 from ot_actual
6 union all
7 select op2_code A,op2_date B
8 from ot_actual
9 union all
10 select op3_code A,op3_date B
11 from ot_actual
12 )
13 where A in('AM1','IR','TPS')
14 group by A
15 order by A;
OP_CODE OP_DATE
------------ -----------
AM1 30-MAY-2020
IR 01-MAY-2015
TPS 31-MAY-2014
So where did the 4th row go?
It is not there in the output because you have grouped by the rows. And that's what Cookiemonster told you that your is wrong. It was just giving similar results because of the data.
Hope you are clear with your doubts now.
[Updated on: Sat, 08 March 2014 13:35] by Moderator Report message to a moderator |
|
|
|
| Re: MAX value of column [message #603909 is a reply to message #603761] |
Fri, 20 December 2013 05:13 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
@msol25: I am not LittleFoot, anyway
Are you able to read and understand the original question? If so, why (=based on which rule included there except the result set) did you introduce the WHERE clause?
Do you think it would also return correct result after changing OP3_DATE to '31-MAY-2015' in the secondly added row (where OP3_CODE='HI')? Have you tried to run queries provided by martijn and Solomon? What do you think about their result after this change?
Additionally, in comparison with LKBrwn_DBA's query, do you think it is better to full scan OT_ACTUAL table three times (especially when it is quite huge)?
@m.abdulhaq: Yes, you may use any date older than the lowest date in *_DATE columns. As nobody knows, which it may be, Solomon used the lowest one supported by Oracle:
SQL> select to_char( to_date(1,'j'), 'dd-mm-yyyy ad hh24:mi:ss' ) from dual;
TO_CHAR(TO_DATE(1,'J')
----------------------
01-01-4712 BC 00:00:00 |
|
|
|
| Re: MAX value of column [message #603911 is a reply to message #603909] |
Fri, 20 December 2013 05:16 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
msol25 wrote on Fri, 20 December 2013 10:24hi Cookie,
I think you are not moderator and you have no responsibility to say someone code is wrong or not..
1) Yes I am a moderator
2) Even if I wasn't it doesn't mean I can't point out wrong code is wrong
msol25 wrote on Fri, 20 December 2013 10:24
Not do like this and how you can say query is not giving result...
Have u run my query?
I didn't need to, I could tell at a glance it was wrong, I even pointed out the issue:
cookiemonster wrote on Thu, 19 December 2013 09:18That exact code is wrong, OP does not want to group across rows. It works with this specific dataset but will not work in general. Solomon and martijn gave the correct solution.
And here's the OP saying that:
m.abdulhaq wrote on Mon, 16 December 2013 18:50i want to find out which was the latest operation per row as per last date .
So let's see what happens if we add an extra row of data to the sample data above:
insert into ot_actual (op1_code ,op1_date ,op2_code ,op2_date ,op3_code ,op3_date )
VALUES ('AM1','30-MAY-2014','TPS','31-MAY-2015','HI','30-JUN-2012');
Solomon's query gives:
SQL> select case greatest(
2 nvl(op1_date,to_date(1,'j')),
3 nvl(op2_date,to_date(1,'j')),
4 nvl(op3_date,to_date(1,'j'))
5 )
6 when op1_date then op1_code
7 when op2_date then op2_code
8 else op3_code
9 end operation,
10 greatest(
11 nvl(op1_date,to_date(1,'j')),
12 nvl(op2_date,to_date(1,'j')),
13 nvl(op3_date,to_date(1,'j'))
14 ) dt
15 from ot_actual;
OPERATION DT
------------ -----------
AM1 30/05/2015
IR 01/05/2015
TPS 31/05/2014
TPS 31/05/2015
SQL>
Yours gives:
SQL> select A op_code
2 ,to_char(max(B),'DD-MON-YYYY') as op_date
3 from(
4 select op1_code A,op1_date B
5 from ot_actual
6 union all
7 select op2_code A,op2_date B
8 from ot_actual
9 union all
10 select op3_code A,op3_date B
11 from ot_actual
12 )
13 where A in('AM1','IR','TPS')
14 group by A
15 order by A;
OP_CODE OP_DATE
------------ -----------
AM1 30-MAY-2015
IR 01-MAY-2015
TPS 31-MAY-2015
SQL>
Not the same is it?
msol25 wrote on Fri, 20 December 2013 10:24
I think you are telling blindly.
Is that based on anything other than your assumption that you couldn't have got it wrong?
msol25 wrote on Fri, 20 December 2013 10:24
Mind your language.....
I have been minding my language and find the suggestion that I haven't frankly offensive.
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 16:31:07 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
