kalin mintchev wrote:
> hi all...
>
> i have an issue with group by and ordering. apparently group by ignores
> 'order by id DESC'?!
>
> an example is a table that has an id and a category fields. there are a
> few categories under which records can be filed. so what i want is the
> latest record from each category by doing something like:
>
> select id, name from table group by category order by id DESC;
>
> this doesn;t work - it shows me the first record under each category - not
> the latest as specified by DESC?! something is wrong.
>
> i tried 'distinct' but that 'distincts' on all fields in the query?!?!
> whats the point of distinct if it can not distincts between fields?! in:
>
> select distinct category, id, name from table order by id DESC;
>
> this query distincts on all category, id and name when it should distinct
> only on category....
>
> how do i do that without temporary tables?
You misunderstand group by & distinct and what they do.
If you have some records:
name - chris
name - mary
name - fred
name - chris
and run something like this:
select name, count(*) from table group by name;
it will return:
chris -> 2
mary -> 1
fred -> 1
*then* if you have an order by mysql will order the results that are
returned by that query:
select name, count(*) from table group by name order by name desc;
it will return:
mary -> 1
fred -> 1
chris -> 2
Basically you can't do what you want either without temporary tables or
using a subselect. Subselects are only available in mysql 4.1+ (I think
- check the docs) so that may or may not be an option.
