GROUP BY or ORDER BY

[dace]

Hi,
Can anyone clear up the difference between the GROUP BY and ORDER BY clauses.

My understanding is that the GROUP BY clause is used in conjunction with certain operators such as COUNT, MAX, MIN etc. and can exclude certain resutls with the HAVING clause.

The ORDER BY clause is used only to sort result that have not been operated upon in ascending or descending order.

Is that correct?? Is there a more specific description??

Thanks much.

[redemption]

The GROUP BY clause allows you to group rows by a field or fields with common values. You can then use the HAVING clause to impose further restrictions on the records returned, but these are group-level conditions, unlike conditions in the WHERE clause which are record-level conditions.

A query without a GROUP BY clause is essentially grouped by it's primary key (if there is one).

ORDER BY merely imposes a sort order on the final results.

Does that help any?

[r937]

> The ORDER BY clause is used only to sort result
> that have not been operated upon in ascending or
> descending order.

actually, there is no way to know which way (ascending, descending, or random) a result set is produced

ORDER BY is required if you want a specific sequence -- if there's no ORDER BY, you get the result in an unspecified sequence

that said, if there's a GROUP BY but no ORDER BY, most databases will return results in GROUP BY sequence, but i caution you not to rely on this

> A query without a GROUP BY clause is essentially
> grouped by it's primary key (if there is one).

um, no

if there is no GROUP BY, then the entire result set is one large group

you can demonstrate this for yourself with something like SELECT COUNT(*) FROM YOURTABLE -- if this table has a primary key, you don't want one group per key, you want one group for the whole table

rudy

[redemption]

That's right. Why did I say that for? My bad

Thanks rudy for the correction.