Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    125

    Unanswered: Order by in the rollup

    Ok, I want to know if it is possible to do an order by while using the rollup in the group by. Look at the below script:

    Code:
    create table  tmpTable 
    (
    prod_name varchar(50) null,
    prod_color varchar(50) null,
    quantity int null
    )
    
    insert into tmpTable values ('table', 'blue', 12)
    insert into tmpTable values ('table', 'red', 100)
    insert into tmpTable values ('table', 'white', 50)
    insert into tmpTable values ('chair', 'blue', 12)
    insert into tmpTable values ('chair', 'red', 1)
    insert into tmpTable values ('chair', 'white', 123)
    insert into tmpTable values ('chair', 'yellow', 50)
    
    SELECT CASE WHEN (GROUPING(prod_name) = 1) THEN 'Grand Total'
                ELSE ISNULL(prod_name, 'UNKNOWN')
           END AS Item,
           CASE WHEN (GROUPING(prod_color) = 1) and grouping(prod_name) != 1 THEN 'Sub Total'
    	    when grouping(prod_color) = 1 and grouping(prod_name) = 1 then ''
                ELSE ISNULL(prod_color, 'UNKNOWN')
           END AS Color,
           SUM(quantity) AS QtySum
    FROM tmpTable
    GROUP BY prod_name, prod_color WITH ROLLUP
    
    --drop table tmpTable
    I want to be able to do an order by for each section of the rollup so that the quantity can be asc for both the chair part of the query and the table part.

    Thanks ahead of time.

    DMW

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can try to use ORDER BY 1, 2 or something like that, but I am not sure you'll get what you expect.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    May 2004
    Posts
    125
    No, sadly its not. I've been playing around with it and I've come to determine that the rollup puts in the extra records in the rs and then sql server does the order by. I'll just have to live with what I have. Thanks anyway.

Posting Permissions

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