Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2008
    Posts
    1

    Question Unanswered: GROUP BY Precedence

    How is the column precedence determined when more then one column is included in the group by clause? I am using T-SQL.

    for example:

    SELECT VendorName, VendorState, SUM(Invoices.InvoiceTotal) AS InvoiceSum
    FROM Vendors JOIN Invoices ON
    Vendors.VendorID = Invoices.InvoiceID
    GROUP BY VendorState, VendorName

    I would expect this statement to group by VendorState first but my result set is grouping by VendorName and then VendorState.

    Am I missing something?
    Last edited by DimSum; 03-13-08 at 23:09.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The result set returned from SQL Server to the client application is sorted by the columns listed in the GROUP BY clause, in the order that the columns are listed in the GROUP BY clause. Once the result set gets to the client application, it can be resorted there however the application chooses.

    To verify this, submit your query using the "thinnest" tool you can find. OSQL.EXE would be my first choice, then SQL Server Management Studio (for SQL 2000 or later), or Query Analyzer (for SQL 7.0 or earlier).

    -PatP

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    The result set returned from SQL Server to the client application is sorted by the columns listed in the GROUP BY clause, in the order that the columns are listed in the GROUP BY clause.
    please refer to post #1

    he's telling you that it is not actually doing that

    note that no sequence is guaranteed unless you use an ORDER BY clause

    or so the SQL Server gurus keep telling me

    the fact that most of the time, the order returned just happens to be the order in the GROUP BY clause, is irrelevant
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sorry, that was a total brain fart on my part. I read ORDER BY instead of GROUP BY.

    -PatP

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    If it were any other platform, it would sort them

    GROUP BY, DISTINCT, ORDER BY causes a sort in DB2 for example

    I thought there was a 4th one...but now I can't remember
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •