[SQL Server 2005] Problem with ORDER BY clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

[SQL Server 2005] Problem with ORDER BY clause

Author

Topic

cms9651
Starting Member

28 Posts

Posted - 2012-09-06 : 10:29:34

[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:


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:


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.


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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-06 : 12:00:45

Add another condition to your ORDER BY clause:

...
ORDER BY
	[DVD] DESC,
	CASE
WHEN MAT IS NULL THEN
	1
ELSE
	0
END,
[SALES];

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources