I want to first get some result set (that includes two joins and selection), and then get the maximum value for one of the columns in the result set. I need both the data in the original results set, and the max. Is this possible with JDBC, and how?
views:
125answers:
2
A:
You can do it with standard SQL although it's a bit awkward:
SELECT a, b, c, (SELECT MAX(c) FROM table1 JOIN table2 ON table1.id = table2.table_id) max_c
FROM table1
JOIN table2 ON table1.id = table2.table_id
cletus
2009-09-08 09:27:47
thanks, I am aware of that method, but I would like to know whether it's possible to actually pefrom it in JDBC with two consecutive queries so as to not add the redundant column to the results.
noam
2009-09-08 09:32:18
A:
I think it is. Should look like this:
SELECT MAX(derivedTable.myRow) FROM
(SELECT * FROM table1 JOIN table2 ON table1.id = table2.some_id) derivedTable
The key is to assign your inner select an alias ("derivedTable" above) and perform another selection on that.
--- Edit based on comment:
No, I don't think that's possible. Even without the JDBC layer - say, in a direct SQL console - I don't think there is a way to query data in a result set in any RDBMS I know.
Depending on the speed of your query and the size of the result, either performing a second query or just iterating through the results to find the maximum are your best options.
Henning
2009-09-08 09:32:40
In that case I didn't understand what you were after. So you want one query, then get the results, and then get the maximum of one column of that previous query result - the motivation being that you don't want to repeat the first query, as you've already performed it?
Henning
2009-09-08 09:52:20