Date:         Thu, 4 Aug 2005 21:44:50 -0400
Reply-To:     "Droogendyk, Harry" <harry.droogendyk@RBC.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Droogendyk, Harry" <harry.droogendyk@RBC.COM>
Subject:      Re: PROC SQL: Does GROUP BY imply ORDER BY?
Content-Type: text/plain; charset="iso-8859-1"

Yes, SAS does order the dataset by the GROUP BY variables, even if unasked to do so, but that's a handy feature IMHO.  However, many RDBMSs don't, eg. Teradata which requires that you also specify a ORDER BY if you want your resulting data in GROUP BY order.

-----Original Message-----
From: owner-sas-l@listserv.uga.edu
[mailto:owner-sas-l@listserv.uga.edu]On Behalf Of Talbot Michael Katz
Sent: Thursday, August 04, 2005 9:39 PM
To: SAS-L@LISTSERV.UGA.EDU; Sigurd Hermansen
Cc: Talbot Michael Katz
Subject: Re: PROC SQL: Does GROUP BY imply ORDER BY?


Hi Sig.

Yes, I would expect the standard for SQL GROUP BY to specify grouping
only.  What caught my attention, though, was that PROC SQL did an ORDER BY
that wasn't asked for, and even registered that fact in the data set's
metadata; it wasn't just an accident.

--  TMK  --
"The Macro Klutz"


On Thu, 4 Aug 2005 20:19:38 -0400, Sigurd Hermansen <HERMANS1@WESTAT.COM>
wrote:

>Talbot:
>GROUP BY may or may not order a dataset by the GROUP BY list. ORDER BY
orders the yield (result) of a query by the ORDER BY list. This means that
ORDER BY always supercedes any ordering that a query happens to impose on
its yield.
>Sig
>
>________________________________
>
>From: owner-sas-l@listserv.uga.edu on behalf of Talbot Michael Katz
>Sent: Thu 8/4/2005 6:15 PM
>To: SAS-L@LISTSERV.UGA.EDU
>Cc: Talbot Michael Katz
>Subject: PROC SQL: Does GROUP BY imply ORDER BY?
>
>
>
>Hi.
>
>Does a GROUP BY in PROC SQL always result in ORDER BY of the group
>variables unless a different ordering is specified in an ORDER BY clause?
>I just did a PROC SQL with "GROUP BY v1, v2" and "ORDER BY v1" only, but
>the PROC CONTENTS told me that the data set was ordered by both v1 and
>v2.  If I leave the ORDER BY off, I get the same thing.  If I explicitly
>do "ORDER BY v2" only, it orders by v2 AND v1.  If I ORDER BY a variable
>not in the GROUP BY clause, then it orders by that variable only.  If I
>ORDER BY a subset of the GROUP BY variables, will it always try to use
>every variable in the GROUP BY clause in the ordering?
>
>--  TMK  --
>"The Macro Klutz"
__________________________________________________________________________________________________________________________________

This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations.
Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized.
If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.

Ce courrier électronique est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent.
Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite.
Si vous recevez ce courrier électronique par erreur, veuillez m'en aviser immédiatement, par retour de courrier électronique ou par un autre moyen.