Hello,
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
Sven
|
What's your sql sample? group by is prior than order by.
|
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.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.name, "+
"bestellungen.liefertermin_bestaetigt, bestellungen_pos.artnr, bestellungen_pos.pos, bestellungen_pos.bez3, bestellungen_pos.typ, bestellungen_pos.einheit "+
"order by "+corder;
Cheers,
Sven
|
"order by "+corder;
If corder is using in group by, it will be ignored. Otherwise, the order by should be used.
|