Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v6.0
  HXTT Cobol v3.0
  HXTT DBF v6.0
  Buy Now
  HXTT Excel v5.0
  HXTT Paradox v6.0
  HXTT Text(CSV) v6.0
  HXTT XML v2.0
Offshore Outsourcing
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
Heng Xing Tian Tai Ltd. of Beijing 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


Copyright © 1999-2017 Heng Xing Tian Tai Ltd. of Beijing City. | All Rights Reserved. | Privacy | Legal | Sitemap