Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v7.1
  HXTT Cobol v5.0
  HXTT DBF v7.1
  Buy Now
  HXTT Excel v6.1
  HXTT Json v1.0
  HXTT Paradox v7.1
  HXTT PDF v2.0
  HXTT Text(CSV) v7.1
  HXTT Word v1.1
  HXTT XML v4.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 Lab of Xi'an 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 © 2003-2019 Heng Xing Tian Tai Lab of Xi'an City. | All Rights Reserved. | Privacy | Legal | Sitemap