Difficult SQL Qn

[ashcarrot]

Hi i have a table like

Test_id, failed, passed, na

where the data would be something like

101, 3, 2, 4
101, 2,7,8
101, 45,6,7
102, 234,5,3
102, 3,23,2

etc....

i want to idealy return the test_id and the total in each column for the for the biggest 5 (or however many) tests that failed (there is 2-3 thousand tests and the test_id is unique)

So in this case something like

101,50,15,19
102,237,28,5

If it can't be done (returning the biggest 5 values) just return all values where the failed,passsed and na values have been added up.

Im using java and JDBC ultimately with SQL so i could possible work out the highest 5 values myself in java though doing it in SQL would save the headache and time

p.S. i dont want to do anything that alters the DB consider read-access only

 

[lewisp]

Try this

[tt]SELECT * FROM (
SELECT test_id,
Sum(failed) failed,
Sum(passed) passed,
Sum(na) na
FROM table
GROUP BY test_id
ORDER BY 2,3,4 )
WHERE rowid <= 5;[/tt]

 

[muthukumar1234]

rank() & dense_rank() function is available in oracle 8ionwards try that.
use this

http://gethelp.devx.com/techtips/oracle_pro/10min/10min1200/10min1200-4.asp