How do I group a query after ordering it?

I'm trying to group a query after i order it, however, in sql (im using DB2) we can't group by after doing order, but if I order after grouping the grouping changes. The reason for this is because its grouped based on multiple attributes not just 1 (i only need to group by 1 attribute but since we are required to match the select statement I have to group by multiple attributes.

I tried doing a virtual table using "with" and tried to order within that but DB2 says that I can only group and not order inside the with attribute

The same goes in the from statement . I tried doing a nested query inside the from attribute which returns the ordered result but DB2 restricts us from ordering inside the from attribute (only grouping is allowed)

So I'm not sure how to do this, how can I group something after ordering it?

Here is a 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 what I'm trying to do is list the pokemon table with: ORDER BY age, name

but after that I want the result to be grouped by their id however Im selecting their id, name, age 开发者_如何学Goand status so when grouping I have to match those attributes:

GROUP BY pokedex.id age status.....

so the problem is if I order after the grouping it breaks the grouping of pokedex.id since its grouped by status and other attributes aswell

Just an example, not really building anything related to pokemon :P

I can see how this might be a problem if you want to ORDER BY variables that you are not wanting to GROUP BY. Not including certain variables in your GROUP BY statement would eliminate those columns FROM the resultant table, and hence you would not be able to order it by them.

In your example, you might want to show how many times a given Pokemon had a certain status, like so:

SELECT
   p.name
   ,d.status
   ,count(*) tally
FROM pokemon p
   INNER JOIN pokedex d on p.name=d.id
GROUP BY
   p.name
   ,d.status

You couldn't order the above query result by 'age' since you didn't GROUP BY name, status, and age.

In this example, I would do:

SELECT
   p.age
   ,p.name
   ,d.status
   ,count(*) tally
FROM pokemon p
   INNER JOIN pokedex d on p.name=d.id
GROUP BY
   p.age
   ,p.name
   ,d.status

However, and to your point, it is not always possible to include in the 'group by' clause those columns you would want to order the data by. So, taking your Pokemon data/tables as my example let me illustrate a general approach that might be helpful:

1) First, I would create a table with my custom ordering:

SELECT
   *
   ,ROW_NUMBER() as my_order
FROM (
   SELECT
      age
      ,name
   FROM pokemon
   ORDER BY
      age
      ,name
)

2) Then, I'd join it to the rest of my grouped table, ordering by the custom 'my_order' column I had created for this purpose:

SELECT
   a.*
   ,custom.my_order
FROM a   -- I'm calling 'a' your table, after your joins/group bys
  INNER JOIN (
    SELECT
       name
       ,ROW_NUMBER() as my_order
    FROM (
        SELECT
           age
           ,name
        FROM pokemon
        ORDER BY
           age
           ,name
    ) 
) AS custom ON a.name=custom.name
ORDER BY custom.my_order

This is my version of 'grouping after doing an ordering', though I don't think about it in those terms - I think of it as a 'custom' ordering.