Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


[SQL Server 2005] Problem with ORDER BY clause


[SQL Server 2005] Problem with ORDER BY clause

Author
Message
cms9651
cms9651
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 144
[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]
   Wink 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


Nesuke
Nesuke
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 83

GROUP BY
            (MAT),
            [DVD] WITH ROLLUP


Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
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)
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 5478
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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 5478
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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
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)
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 5478

...
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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
cms9651
cms9651
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 144
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


Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
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)
cms9651
cms9651
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 144
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search