Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v5.2
  HXTT Cobol v2.1
  HXTT DBF v5.2
  Buy Now
  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)

-order by- not taken into account?
2014-05-21 02:24:13.0

I am facing a rather strange issue with an -order by- clause of an SQL statement being ignored. I have a result set that I want to order by different columns. But what ever order by I use I get the same result order and this result order seems to be related to the -group by- clause that I am using.

Are you aware of this? Is the -order by- of a SQL select ignored if a -group by-
is used at the same time?

I even used the latest TRIAL version of your driver, DBF JDBC 3.0 Package V5.2,
an got the same results.

Thank you for any help

Re:-order by- not taken into account?
HXTT Support
2014-05-21 02:53:55.0
What's your sql sample? group by is prior than order by.
Re:Re:-order by- not taken into account?
2014-05-21 07:46:28.0
This is my SQL string including a dynamic order by using the variable "corder". I thought at least in Visual Foxpro the order by is prior to group by but if your driver is different I should be able to change the code and will sort by using the group by.

String cConnectionSQL = "select "+
"bestellungen.bestellung, bestellungen.sch, bestellungen.b1, bestellungen.verladung, bestellungen.sped_name,,"+
"bestellungen.liefertermin_bestaetigt, bestellungen_pos.liefertermin, bestellungen_pos.artnr, bestellungen_pos.pos, bestellungen_pos.bez1, bestellungen_pos.bez2, bestellungen_pos.bez3, bestellungen_pos.typ, bestellungen_pos.einheit, "+
"sum(bestellungen_pos.gewicht) as gewicht, "+
"sum(bestellungen_pos.menge) as menge, "+
"sum(bestellungen_pos.menge_geliefert) as menge_geliefert, "+
"sum(bestellungen_pos.menge_verlust) as menge_verlust, "+
"sum(iif(isnull(aggregate.menge_fertig),0,aggregate.menge_fertig)) "+
"from bestellungen "+
"inner join bestellungen_pos on upper(bestellungen.bestellung)==upper(bestellungen_pos.bestellung) "+
"left join (select bestellung, pos, SUM(iif(isnull(menge_fertig),0,menge_fertig)) as menge_fertig from bestellungen_fertigmeldungen group by bestellung, pos) aggregate "+
"on aggregate.bestellung IS NULL or aggregate.pos is NULL or (upper(aggregate.bestellung)=upper(bestellungen.bestellung) and bestellungen_pos.pos=aggregate.pos) "+
"where bestellungen.erledigt=false and not empty(bestellungen.datum) and bestellungen_pos.erledigt=false and upper(bestellungen.ckdnr)=upper('"+kdnr+"') "+
"group by "+
"bestellungen_pos.liefertermin, bestellungen.bestellung, bestellungen_pos.bez1, bestellungen_pos.bez2, bestellungen.sch, "+
"bestellungen.b1, "+
"bestellungen.verladung, bestellungen.sped_name,, "+
"bestellungen.liefertermin_bestaetigt, bestellungen_pos.artnr, bestellungen_pos.pos, bestellungen_pos.bez3, bestellungen_pos.typ, bestellungen_pos.einheit "+
"order by "+corder;


Re:Re:Re:-order by- not taken into account?
HXTT Support
2014-05-21 07:59:44.0
"order by "+corder;
If corder is using in group by, it will be ignored. Otherwise, the order by should be used.

Search Key   Search by Last 50 Questions


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