Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2012
    Posts
    30

    Unanswered: [SQL Server 2005] Problem with ORDER BY clause

    [SQL Server 2005] Problem with ORDER BY clause

    Hello everyone, I hope your help.

    I have problem with order the output in this query.

    The output now is:
    Code:
    MAT	DVD	SALES
    MAC	L12	3
    MAE	L12	3
    MAS	L12	3
    Tot	L12	9
    MAC	L01	1
    Tot	L01	5
    MAS	L01	4
    I need instead this other correct output:
    Code:
    MAT	DVD	SALES
    MAE	L12	3
    MAC	L12	3
    MAS	L12	3
    Tot	L12	9
    MAC	L01	1
    MAS	L01	4
    Tot	L01	5
    Can you help me?
    Thank you in advance, your help is very appreciated.
    Code:
    SELECT
    	[MAT],
    	[DVD],
    	[SALES]
    FROM
    	(
    		SELECT
    			[MAT],
    			[DVD],
    			[SALES]
    		FROM
    			TestTable
    		UNION
    			SELECT
    				COALESCE ([MAT], 'Tot') AS [MAT],
    				[DVD],
    				SUM ([SALES])
    			FROM
    				TestTable
    			GROUP BY
    				(MAT),
    				[DVD]
    	) q
    ORDER BY
    	[DVD] DESC,
    	CASE
    WHEN MAT IS NULL THEN
    	1
    ELSE
    	0
    END;
    
    
    
    
    
    
    
    -- ----------------------------
    -- Table structure for [dbo].[TestTable]
    -- ----------------------------
    DROP TABLE [dbo].[TestTable]
    GO
    CREATE TABLE [dbo].[TestTable] (
    [MAT] varchar(50) NULL ,
    [DVD] varchar(50) NULL ,
    [SALES] int NULL
    )
    
    
    GO
    
    -- ----------------------------
    -- Records of TestTable
    -- ----------------------------
    INSERT INTO [dbo].[TestTable] ([MAT], [DVD], [SALES]) VALUES (N'Tot', N'L01', N'5');
    GO
    INSERT INTO [dbo].[TestTable] ([MAT], [DVD], [SALES]) VALUES (N'MAC', N'L12', N'3');
    GO
    INSERT INTO [dbo].[TestTable] ([MAT], [DVD], [SALES]) VALUES (N'MAS', N'L01', N'4');
    GO
    INSERT INTO [dbo].[TestTable] ([MAT], [DVD], [SALES]) VALUES (N'MAE', N'L12', N'3');
    GO
    INSERT INTO [dbo].[TestTable] ([MAT], [DVD], [SALES]) VALUES (N'MAC', N'L01', N'1');
    GO
    INSERT INTO [dbo].[TestTable] ([MAT], [DVD], [SALES]) VALUES (N'Tot', N'L12', N'9');
    GO
    INSERT INTO [dbo].[TestTable] ([MAT], [DVD], [SALES]) VALUES (N'MAS', N'L12', N'3');
    GO

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Try:

    Code:
    ORDER BY [DVD] DESC, [MAT]
    Hope this helps.

  3. #3
    Join Date
    Aug 2012
    Posts
    30
    Thank you!

    thank you for help.

    Total is it already in a table TestTable (in post #1 I have posted the CREATE TABLE [dbo].[TestTable] code).

    I tried your query but I have this incorrect output:
    Code:
    MAT	DVD	SALES
    MAC	L12	3
    MAE	L12	3
    MAS	L12	3
    Tot	L12	9
    MAC	L01	1
    MAS	L01	4
    Tot	L01	5
    I need this:
    Code:
    MAT	DVD	SALES
    MAE	L12	3
    MAC	L12	3
    MAS	L12	3
    Tot	L12	9
    MAC	L01	1
    MAS	L01	4
    Tot	L01	5
    Last edited by cms9651; 09-06-12 at 12:50.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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