Hmm - why isn't this query sorting properly?

[bunner_bob]

Seems simple enough:

SELECT DISTINCT projects.project_id, projects.project_title, projects.client_code, projects.status, projects.proposal_amount, projects.notes FROM projects WHERE (projects.project_id BETWEEN 1100 AND 1120) ORDER BY projects.client_code ASC

And yet, sort it does not - or rather, it seems to be sorting on the primary key (project_id), except when I expand the BETWEEN range I discover that it's sorting on I don't know what.

If I sort on project_id or project_id DESC it works every time, but client code (which is a four-character alpha - all caps) doesn't do it.

Jes' plain weird...

[tommi811]

which database?
can you post a sample output?

thx

[bunner_bob]

Solved it - though I don't understand the mechanics (MySQL 4.1 by the way).

My query is modular - depending on what the user requests it adds additional "where" conditions, and in some cases queries a left join of tables instead of a single table - hence the DISTINCT.

Apparently getting rid of the DISTINCT when I'm not doing a join fixes the problem.

Anybody know why?

[Roger_Ramjet]

MySQL bug probably, should not matter and would not in the other dbs that I am familiar with.