Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server Newbies » [SQL Server 2005] Problem with ORDER BY...
Add to briefcase
13 posts, Page 1 of 2
12»»

[SQL Server 2005] Problem with ORDER BY clause Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
cms9651
Posted Thursday, September 06, 2012 8:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 5:01 AM
Points: 42, Visits: 121
[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
Post #1355350
Nesuke
Posted Thursday, September 06, 2012 8:48 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 11, 2013 2:31 AM
Points: 8, Visits: 26 
GROUP BY
				(MAT),
				[DVD] WITH ROLLUP
Post #1355371
Sean Lange
Posted Thursday, September 06, 2012 9:04 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 8:46 AM
Points: 8,547, Visits: 8,204
The rollup option suggested previously is not what you want here. I have to admit I don't quite understand why your query is so complicated. It seems you introduced a subquery when it is not needed. You can add an order by to the end of your original query. In fact I am not sure why you even need the union at all.

This query will produce the same results:

SELECT
	COALESCE ([MAT], 'Tot') AS [MAT],
	[DVD],
	SUM ([SALES])
FROM
	TestTable
GROUP BY
	(MAT),
	[DVD] --with rollup
	ORDER BY
[DVD] DESC

There is no point in adding the case in the order by. None of the values are null. You have ensured that using the coallesce so the case in the order by is pointless.



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


You said you want your output in the above order. This does not appear to be ordered by anything other than DVD. The MAT column is not ordered by anything, DVD is of course the same, and sales is not unique enough to have a valid order here. Why is MAE before MAC??

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1355387
Eugene Elutin
Posted Thursday, September 06, 2012 9:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:59 AM
Points: 2,525, Visits: 4,324
You show in your setup that your table already holds the calculated Total record (looks like it is total per DVD). Then to sort results as you want you need to do simply that:


SELECT [MAT],
           [DVD],
           [SALES]
FROM TestTable
ORDER BY DVD DESC
             ,CASE WHEN MAT = 'Tot' THEN 1 ELSE 0 END
             ,MAT




_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1355392
Eugene Elutin
Posted Thursday, September 06, 2012 9:10 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:59 AM
Points: 2,525, Visits: 4,324
Sean Lange (9/6/2012)

...
This query will produce the same results:

SELECT
	COALESCE ([MAT], 'Tot') AS [MAT],
	[DVD],
	SUM ([SALES])
FROM
	TestTable
GROUP BY
	(MAT),
	[DVD] --with rollup
	ORDER BY
[DVD] DESC

...


Try to add one more data row: 
 INSERT INTO [dbo].[TestTable] ([MAT], [DVD], [SALES]) VALUES (N'WAS', N'L12', N'3');

To ensure that 'Tot' record is placed as the last per DVD, you should treat it separately eg. 
ORDER BY [DVD] DESC, CASE WHEN MAT = 'Tot' THEN 1 ELSE 0 END, MAT



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1355395
Sean Lange
Posted Thursday, September 06, 2012 9:12 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 8:46 AM
Points: 8,547, Visits: 8,204
Eugene Elutin (9/6/2012)
Sean Lange (9/6/2012)

...
This query will produce the same results:

SELECT
	COALESCE ([MAT], 'Tot') AS [MAT],
	[DVD],
	SUM ([SALES])
FROM
	TestTable
GROUP BY
	(MAT),
	[DVD] --with rollup
	ORDER BY
[DVD] DESC

...


Try to add one more data row: 
 INSERT INTO [dbo].[TestTable] ([MAT], [DVD], [SALES]) VALUES (N'WAS', N'L12', N'3');

To ensure that 'Tot' record is placed as the last per DVD, you should treat it separately eg. 
ORDER BY [DVD] DESC, CASE WHEN MAT = 'Tot' THEN 1 ELSE 0 END, MAT



True but as I said there seems to be a bit of clarity needed on what the order should be. The MAT column is not ordered in the desired output.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1355397
Eugene Elutin
Posted Thursday, September 06, 2012 9:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:59 AM
Points: 2,525, Visits: 4,324

...
True but as I said there seems to be a bit of clarity needed on what the order should be. The MAT column is not ordered in the desired output.


then just:


ORDER BY [DVD] DESC, CASE WHEN MAT = 'Tot' THEN 1 ELSE 0 END


The MAT column will be ordered randomly, but Tot will still come as a last one per DVD.
I agree, that setup needs clarification about Total: is it already in a table or needs to be calculated?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1355412
cms9651
Posted Thursday, September 06, 2012 9:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 5:01 AM
Points: 42, Visits: 121
Eugene Elutin (9/6/2012)
[quote]
The MAT column will be ordered randomly, but Tot will still come as a last one per DVD.
I agree, that setup needs clarification about Total: is it already in a table or needs to be calculated?


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:
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:
MAT	DVD	SALES
MAE	L12	3
MAC	L12	3
MAS	L12	3
Tot	L12	9
MAC	L01	1
MAS	L01	4
Tot	L01	5
Post #1355440
Sean Lange
Posted Thursday, September 06, 2012 9:57 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 8:46 AM
Points: 8,547, Visits: 8,204
cms9651 (9/6/2012)
Eugene Elutin (9/6/2012)
[quote]
The MAT column will be ordered randomly, but Tot will still come as a last one per DVD.
I agree, that setup needs clarification about Total: is it already in a table or needs to be calculated?


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:
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:
MAT	DVD	SALES
MAE	L12	3
MAC	L12	3
MAS	L12	3
Tot	L12	9
MAC	L01	1
MAS	L01	4
Tot	L01	5



Right and I have said repeatedly we need to understand what the rule for the sorting is. The only difference is that MAE is before MAC. What makes up the rule for ordering?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1355447
cms9651
Posted Thursday, September 06, 2012 10:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 5:01 AM
Points: 42, Visits: 121
The rule for the sorting is mat geographic NAME.

MAW = My Area West
MAE = My Area East
MAC = My Area Center
MAS = My Area South
Post #1355448
« Prev Topic | Next Topic »

Add to briefcase
13 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.