Very Computer

How do you use ORDER BY with an aggregate query?  I have a query that is
something like:

SELECT this, sum(that) AS theother
FROM atable
GROUP BY this

what I want to do is append ORDER BY to rank the summed values.

Anyone know how to do that?

Sidart

Have you try to append at the end :
ORDER BY sum(that)

Quote:> How do you use ORDER BY with an aggregate query?  I have a query that is
> something like:

> SELECT this, sum(that) AS theother
> FROM atable
> GROUP BY this

> what I want to do is append ORDER BY to rank the summed values.

> Anyone know how to do that?

> Sidart

Why can't you just add an order by clause

SELECT this, sum(that) AS theother
FROM atable
GROUP BY this
ORDER BY sum(that)

As long as the field you are ordering by is in the select list you should be
fine.

Regards,

Paul

If you are using SQL Server you can't put an ORDER BY clause on
a query with an aggregate function without hacking around.
But using ADO, use a client side cursor, then set the recordset's
sort property to "theother" after opening it.
If you are not quite sure what I'm talking about, email me and
I'll send you some source code.

Jaosn

Quote:> How do you use ORDER BY with an aggregate query?  I have a query that is
> something like:

> SELECT this, sum(that) AS theother
> FROM atable
> GROUP BY this

> what I want to do is append ORDER BY to rank the summed values.

> Anyone know how to do that?

> Sidart

You can also sort by using the position of the column..

select a,b,c,d
from f
order by 1,2

Not the best of things to do as it is all to easy to forget to change the
sort parameter if the select list changes but very good if you are building
SQL in code with variable lists.

Tony