"group by" - order of rows in group

[aljosa.mohorovic@gmail.com]

this is simple example:

TABLE SCHEMA:
create table my_table(
id int unsigned not null auto_increment primary key,
project varchar(255) not null,
title varchar(255) not null
);

TABLE DATA:
1 | project_1 | revision_1
2 | project_2 | revision_1
3 | project_1 | revision_2

QUERY:
select id,project,title from my_table group by project order by title
asc;

RESULT:
1 | project_1 | revision_1
2 | project_2 | revision_1

WHAT I WANT:
2 | project_2 | revision_1
3 | project_1 | revision_2

MY PROBLEM:
from rows in groups, example for "project_1" group rows:
1 | project_1 | revision_1
3 | project_1 | revision_2
i want row with highest id to be part of result.

i currently achieve this by calling "alter table researches order by id
desc" before select.

Aljosa Mohorovic

[strawberry]

[email]aljosa.mohorovic@gmail.com[/email] wrote:

> this is simple example:
>
> TABLE SCHEMA:
> create table my_table(
> id int unsigned not null auto_increment primary key,
> project varchar(255) not null,
> title varchar(255) not null
> );
>
> TABLE DATA:
> 1 | project_1 | revision_1
> 2 | project_2 | revision_1
> 3 | project_1 | revision_2
>
> QUERY:
> select id,project,title from my_table group by project order by title
> asc;
>
> RESULT:
> 1 | project_1 | revision_1
> 2 | project_2 | revision_1
>
> WHAT I WANT:
> 2 | project_2 | revision_1
> 3 | project_1 | revision_2
>
> MY PROBLEM:
> from rows in groups, example for "project_1" group rows:
> 1 | project_1 | revision_1
> 3 | project_1 | revision_2
> i want row with highest id to be part of result.
>
> i currently achieve this by calling "alter table researches order by id
> desc" before select.
>
> Aljosa Mohorovic

This question gets asked a lot!

Here's a generic answer.

SELECT t1 . *
FROM table1 t1
LEFT JOIN table1 t2 ON t1.col1 = t2.col1
AND t1.col2 < t2.col2
WHERE t2.col2 IS NULL;