We help IT Professionals succeed at work.
Get Started

selecting a max within a max oracle query.

Sanmarie
Sanmarie asked
on
9,561 Views
Last Modified: 2013-12-19
Using oracle 8.
I am using the following query in an oracle function but I want to know if it could be written better. I have a table "my table" which has two sequence numbers "sequence_no" and "seq_no." I want to find the maximum "sequence_no" for a particular key "my_key". But when I find this max sequence_no, I have to find the maximum seq_no within that maximum sequence_no. HOpe I didn't confuse you. Help and Thanks

SELECT decode(my_value,'Y','Fuel')
                  INTO ls_temp
                  FROM  my_table
                  WHERE
                        util_name = 'h$_fuel'
                        AND my_value = 'Y'
                        AND key = my_key
                        AND sequence_no =
                              (SELECT max(sequence_no)
                               FROM my_table
                               WHERE util_name = 'h$_fuel' AND key = my_key
                               )
                        AND seq_no =
                              (SELECT max(seq_no)
                              FROM my_table
                              WHERE util_name = 'h$_fuel' AND key = my_key
                              AND sequence_no =
                                    (SELECT max(sequence_no)
                                          FROM my_table
                                          WHERE util_name = 'h$_fuel' AND key = my_key
                                    )
                              );

Comment
Watch Question
Commented:
This problem has been solved!
Unlock 2 Answers and 5 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE