Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

[SQL Server 2005] Problem with ORDER BY clause Expand / Collapse
Author
Message
Posted Thursday, September 6, 2012 8:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 20, 2014 9:07 AM
Points: 52, Visits: 131
[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
Posted Thursday, September 6, 2012 8:48 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 18, 2014 8:55 AM
Points: 8, Visits: 79
GROUP BY
(MAT),
[DVD] WITH ROLLUP

Post #1355371
Posted Thursday, September 6, 2012 9:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:41 PM
Points: 13,315, Visits: 12,182
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1355387
Posted Thursday, September 6, 2012 9:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 7:27 AM
Points: 2,836, Visits: 5,067
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
Posted Thursday, September 6, 2012 9:10 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 7:27 AM
Points: 2,836, Visits: 5,067
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
Posted Thursday, September 6, 2012 9:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:41 PM
Points: 13,315, Visits: 12,182
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1355397
Posted Thursday, September 6, 2012 9:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 7:27 AM
Points: 2,836, Visits: 5,067

...
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
Posted Thursday, September 6, 2012 9:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 20, 2014 9:07 AM
Points: 52, Visits: 131
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
Posted Thursday, September 6, 2012 9:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:41 PM
Points: 13,315, Visits: 12,182
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1355447
Posted Thursday, September 6, 2012 10:02 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 20, 2014 9:07 AM
Points: 52, Visits: 131
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 12»»

Permissions Expand / Collapse