SQL Query

10 pts.
Tags:
Oracle Database
Oracle developers
SQL Query
Hello everyone, I have the following table: CREATE TABLE MKW ( ID NUMBER, UREN NUMBER, DATUM DATE ); It has the following records: SQL> select * from mkw order by datum,id; ID UREN DATUM ---------- ---------- -------- 1 3 01-12-08 1 2 01-12-08 2 3 01-12-08 2 2 01-12-08 3 6 05-12-08 4 10 05-12-08 5 4 05-12-08 I need to find out per date (field datum) which id has the most value of uren. For datum 01-12-2008 there are two id's: id 1 with a total of 5 hours and id 2 with a total of also 5 hours. I need to select the id with the hours that is the greatest for that datum. If the hours are equal for more than one id then I need to get the smallest id. I have the following statement that works: 1 SELECT MIN(id) 2 , datum 3 FROM (SELECT mkw.id, mkw.datum, SUM(mkw.uren) uren 4 FROM mkw 5 , (SELECT MAX(uren) uren, datum 6 FROM (SELECT id,SUM(uren) uren,datum 7 FROM mkw 8 GROUP BY id, datum 9 ) 10 GROUP BY datum 11 ) mkw2 12 WHERE mkw.datum = mkw2.datum 13 GROUP BY mkw.id, mkw.datum 14 HAVING SUM(mkw.uren) = MAX(mkw2.uren) 15 ) 16* GROUP BY datum SQL> / MIN(ID) DATUM ---------- -------- 1 01-12-08 4 05-12-08 I'm working on an Oracle 8.1.7 database and would like to know if there is a easier way to do this.

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following