Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v5.2
  HXTT Cobol v2.1
  HXTT DBF v5.2
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Excel v4.2
  HXTT Paradox v5.2
  HXTT Text(CSV) v5.2
  HXTT XML v1.2
Offshore Outsourcing
Oracle Data Import/Export
DB2 Data Import/Export
Sybase Data Import/Export
Free Resources
  Firewall Tunneling
  Search Indexing Robot
  Conditional Compilation
  Password Recovery for MS Access
  Password Recovery for Corel Paradox
  Checksum Tool for MD5
  Character Set Converter
  Pyramid - Poker of ZYH
   
   
   
Hongxin Technology & Trade Ltd. of Xiangtan City (abbr, HXTT)

HXTT DBF
Top problem
Fernando Hartmann
2009-05-12 17:07:51.0
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


Re:Top problem
HXTT Support
2009-05-13 08:00:08.0
>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);
Re:Re:Top problem
Fernando Hartmann
2009-05-13 09:42:57.0
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.
Re:Re:Re:Top problem
HXTT Support
2009-05-21 22:10:20.0
TOP isn't a standard sql syntax. Please download the latest package, which will work like SqlServer(Top).

Search Key   Search by Last 50 Questions




Google
 

Address: 9 Station Rd., Xiangtan City, Hunan Province, P.R. China
Postcode: 411100
Phone: (86)731-58225727
Fax: (86)731-58225727
Email: webmaster@hxtt.com
Copyright © 1999-2011 Hongxin Technology & Trade Ltd. | All Rights Reserved. | Privacy | Legal | Sitemap