mysql - How do I group a query after ordering it? -

i'm trying group query after order it, however, in sql (im using db2) can't group after doing order, if order after grouping grouping changes. reason because grouped based on multiple attributes not 1 (i need group 1 attribute since required match select statement have group multiple attributes.

i tried doing virtual table using "with" , tried order within db2 says can group , not order inside attribute

the same goes in statement . tried doing nested query inside attribute returns ordered result db2 restricts ordering inside attribute (only grouping allowed)

so i'm not sure how this, how can group after ordering it?

here sample table:

pokemon:

id name age
3 pikachu 1
2 bulbasure 3
1 charazard 2
11 pikachu 10

(in pokemon table)
primary key id, name
foreign key (name) references pokedex (name) )

pokedex:

id status
pikachu derp
charazard herp
bulbasure burp

now i'm trying list pokemon table with: order age, name
after want result grouped id im selecting id, name, age , status when grouping have match attributes:
group pokedex.id age status.....
problem if order after grouping breaks grouping of pokedex.id since grouped status , other attributes aswell
example, not building related pokemon :p

i can see how might problem if want order variables not wanting group by. not including variables in group statement eliminate columns resultant table, , hence not able order them.

in example, might want show how many times given pokemon had status, so:

select    p.name    ,d.status    ,count(*) tally pokemon p    inner join pokedex d on p.name=d.id group    p.name    ,d.status 

you couldn't order above query result 'age' since didn't group name, status, and age.

in example, do:

select    p.age    ,p.name    ,d.status    ,count(*) tally pokemon p    inner join pokedex d on p.name=d.id group    p.age    ,p.name    ,d.status 

however, , point, not possible include in 'group by' clause columns want order data by. so, taking pokemon data/tables example let me illustrate general approach might helpful:

1) first, create table custom ordering:

 select    *    ,row_number() my_order (    select       age       ,name    pokemon    order       age       ,name ) 

2) then, i'd join rest of grouped table, ordering custom 'my_order' column had created purpose:

 select    a.*    ,custom.my_order   -- i'm calling 'a' table, after joins/group bys   inner join (     select        name        ,row_number() my_order     (         select            age            ,name         pokemon         order            age            ,name     )  ) custom on a.name=custom.name order custom.my_order 

this version of 'grouping after doing ordering', though don't think in terms - think of 'custom' ordering.