Hi.
On Tue, Jun 19, 2001 at 09:01:30AM -0700, [EMAIL PROTECTED] wrote:
> I'm generating a pulldown menu using PHP and MySQL. Originally I had the
> list order by model where it put everything in alphabetical order. I need to
> order them by year of model which luckily coincides with the order that I
> dumped them into the database so I would like to ORDER by id which was an
> auto-increment number. The list work fine when ordering by model but when I
> order by id I pull up more then distinct model names. Here's the query;
>
> $menu_query = "SELECT DISTINCT model"
> . " FROM a_clutch"
> . " WHERE make = \"Harley-Davidson\" AND status = \"active\""
> . " ORDER BY id";
>
> How can I pull up distinct models and order just the distinct models by id?
You have a logical oversight in your query: To be able to ORDER BY id,
the RDBMS has to pass through id silently. And this cannot be resolved
another way: if there are to models named "foobar" with ids 5 and 7
resp. how should the RDBMS know whether to keep 5 or 7 before applying
the ORDER?
So effectively you are asking for
SELECT DISTINCT model, id
FROM a_clutch
WHERE make = "Harley-Davidson" AND status = "active"
ORDER BY id
which explains the result you get.
If there are two models with the same name and id, DISTINCT will be
able to eleminate the second row and therefore the query would work as
expected, if for all same model names, the ids would be the same.
Bye,
Benjamin.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
