Re: [Firebird-devel] Using DISTINCT and ORDER BYtogetherandunpredictable results

[Arno B. <fbd...@ab...>]

Hi Alex,

> Well, let it be more realistic example.
> select t1.somedate, sum(t1.somedouble), t1.t2link
>  from t1 join t2 on t1.t2link = t2.id
> where <some-condition>
> group by t1.somedate, t1.t2link
>  order by t2.somename

I'm sorry, but this is certainly not going to work (in FB1.5 and higher!).
In fact this is exactly the same problem as with DISTINCT, you're trying to order on a field that is unpredictable (can 
hold multiple values in the group "somedate, t2link" ).

An simple example where i use fields only order by is this:

SELECT
  p.ParticipantName,
  o.OrganisationName,
  ts.TotalScore
FROM
  Participants p
  JOIN Organisations o ON (o.OrganisationID = p.OrganisationID)
  JOIN TotalScores ts ON (ts.ParticipantID = p.ParticipantID)
ORDER BY
  ts.SortKey DESC

ts.SortKey is here a large varchar that holds the sorting-key (outcome from a formula) and you don't want to display 
this.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
General database developer support:
  http://www.databasedevelopmentforum.com

Firebird open source database (based on IB-OE) with many SQL-99 features:
  http://www.firebirdsql.org
  http://www.firebirdsql.info

Support list for Interbase and Firebird users:
  fir...@ya...

Nederlandse firebird nieuwsgroep:
  news://newsgroups.firebirdsql.info