SEO

#1 (**permalink**) 02-27-2008, 09:34 PM

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?

thanks...

#2 (**permalink**) 02-27-2008, 09:34 PM

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.

#3 (**permalink**) 02-27-2008, 09:34 PM

>
> 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.

thanks... pardon my ignorance - how would i do that using subselects?

>

#4 (**permalink**) 02-27-2008, 09:34 PM

kalin mintchev wrote:
>> 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.
>
> thanks... pardon my ignorance - how would i do that using subselects?

Without knowing your schema this is just a guess but:

select * from records where categoryid=(select categoryid from
categories order by last_update desc limit 1);

will get the most recently updated category and match it to the
'records' table.

Actually... That could probably be done as a join:

select * from records r inner join categories c on
(r.categoryid=c.categoryid) order by c.last_update desc;

but that will get you all records for that category not just the most
recently updated.

Of course I could be on the completely wrong track because you haven't
fully described what you're trying to get out and what data you have

#5 (**permalink**) 02-27-2008, 09:34 PM

>
> but that will get you all records for that category not just the most
> recently updated.
>

that's the main problem, isn't it?

what i'm looking for is the last record for EACH of the categories in the
table. i'm aware of the aformentioned options.

my problem with group by is that ignores the DESC in order by. if i do:

select id from table group by category;

it does the same as if i do:

select id from table group by category order by id desc;

both queries return the FIRST found record for EACH category in ascending
order - the order they were indexed in. i geuss i still don't get why if i
request 'order by id desc' the 'group by' doesn't look 'backwards'.
obviuosly the 'grouping' comes before the 'ordering' in the query - it
probably is executed that way too. why can't it be the other way around
taking in consideration the request of ordering the results in descending
order and then grouping them by category....

i guess the answer is - just because...

#6 (**permalink**) 02-27-2008, 09:34 PM

On 10/16/06, kalin mintchev <kalin@el.net> wrote:
>
> >
> > but that will get you all records for that category not just the most
> > recently updated.
> >
>
> that's the main problem, isn't it?
>
> what i'm looking for is the last record for EACH of the categories in the
> table. i'm aware of the aformentioned options.
>
> my problem with group by is that ignores the DESC in order by. if i do:
>
> select id from table group by category;
>
> it does the same as if i do:
>
> select id from table group by category order by id desc;
>
> both queries return the FIRST found record for EACH category in ascending
> order - the order they were indexed in. i geuss i still don't get why if i
> request 'order by id desc' the 'group by' doesn't look 'backwards'.
> obviuosly the 'grouping' comes before the 'ordering' in the query - it
> probably is executed that way too. why can't it be the other way around
> taking in consideration the request of ordering the results in descending
> order and then grouping them by category....

The "order by" orders the *results* of the group by. It does not
affect what happens inside the group by.

This is standard across all databases - mysql, postgres, sqlite - all of them.

It can't be done the other way because grouping the results together
will affect ordering (think of aggregate functions especially).

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

group by problem