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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
