Top problem
I found a strange problem using TOP, in the following code I show the problem, the same query is submitted two times to read a table, the only difference is that the second have a TOP 1 clause, the output is
First Query
without top
RS--> 1.095459826E7
MG--> 4367264.69
RJ--> 2908578.92
PE--> 2778536.85
BA--> 1334351.09
SP--> 1069485.04
PR--> 997152.02
Second Query
with top
BA--> 1334351.09
The second query is suppose to show the "RS--> 1.095459826E7" record , correct ?
Bellow the code
package tests;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class Top {
public static void main(String[] args) throws Exception {
String connectionUrl;
Connection con;
connectionUrl = "jdbc:DBF:/table";
Class.forName("com.hxtt.sql.dbf.DBFDriver");
con = DriverManager.getConnection(connectionUrl);
System.out.println("without top");
PreparedStatement ps = con.prepareStatement(
"SELECT Field7, sum(Field12) as Field12 FROM table GROUP BY Field7 ORDER BY sum(Field12) desc");
ResultSet resp = ps.executeQuery();
while (resp.next()) {
System.out.println(resp.getString("Field7") + "--> " + resp.getDouble("Field12"));
}
resp.close();
ps.close();
System.out.println("\nwith top");
ps = con.prepareStatement(
"SELECT top 1 Field7, sum(Field12) as Field12 FROM table GROUP BY Field7 ORDER BY sum(Field12) desc");
resp = ps.executeQuery();
while (resp.next()) {
System.out.println(resp.getString("Field7") + "--> " + resp.getDouble("Field12"));
}
resp.close();
ps.close();
con.close();
}
}
Thanks for your support
|
>The second query is suppose to show the "RS--> 1.095459826E7" record , correct ?
No. Top clause is using to tell engine to return the possible part result ASAP.
If you wish the firt row, you can use the following sql
SELECT top 1 * from (select Field7, sum(Field12) as Field12 FROM table GROUP BY Field7 ORDER BY sum(Field12) desc);
|
Ok, thanks again, I tried and you suggestion worked.
Looking more deeply I concluded that the top is made before the sort of the records (order by), is this correct ?
I surprised with this behavior because other bd like SqlServer(Top) em Postgres(Limit) made to top after the "order by" returning the "correct" response without the sub-select.
|
TOP isn't a standard sql syntax. Please download the latest package, which will work like SqlServer(Top).
|