September 6, 2012 at 8:29 am
[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:
MATDVDSALES
MACL123
MAEL123
MASL123
TotL129
MACL011
TotL015
MASL014
I need instead this other correct output:
MATDVDSALES
MAEL123
MACL123
MASL123
TotL129
MACL011
MASL014
TotL015
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
September 6, 2012 at 8:48 am
GROUP BY
(MAT),
[DVD] WITH ROLLUP
September 6, 2012 at 9:04 am
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:
MATDVDSALES
MAEL123
MACL123
MASL123
TotL129
MACL011
MASL014
TotL015
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 6, 2012 at 9:06 am
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
September 6, 2012 at 9:10 am
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
September 6, 2012 at 9:12 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 6, 2012 at 9:21 am
...
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?
September 6, 2012 at 9:49 am
Eugene Elutin (9/6/2012)
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:
MATDVDSALES
MACL123
MAEL123
MASL123
TotL129
MACL011
MASL014
TotL015
I need this:
MATDVDSALES
MAEL123
MACL123
MASL123
TotL129
MACL011
MASL014
TotL015
September 6, 2012 at 9:57 am
cms9651 (9/6/2012)
Eugene Elutin (9/6/2012)
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:
MATDVDSALES
MACL123
MAEL123
MASL123
TotL129
MACL011
MASL014
TotL015
I need this:
MATDVDSALES
MAEL123
MACL123
MASL123
TotL129
MACL011
MASL014
TotL015
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 6, 2012 at 10:02 am
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
September 6, 2012 at 10:23 am
cms9651 (9/6/2012)
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
Oh, why we couldn't see that from your first post. It is so obvious ... (yes it's a sarcasm) :hehe:
If you understand how I've made 'Tot' to appear as a last one, you should be able to figure out how to force the order based on the above requirement. You will need to hard-code priority, or create and use table which list your Geographic NAMEs where you can maintain the order sequence.
1. Just hard-coded order priority
SELECT
[MAT],
[DVD],
[SALES]
FROM
TestTable
ORDER BY DVD DESC, CASE MAT WHEN 'MAW' THEN 1
WHEN 'MAE' THEN 2
WHEN 'MAC' THEN 3
WHEN 'MAS' THEN 4
ELSE 5 --'Tot'
END
2. With order sequence maintained in dedicated table
CREATE TABLE dbo.RefGeographic (Code CHAR(3), Description VARCHAR(50), OrderSequence INT)
INSERT dbo.RefGeographic SELECT 'MAW','My Area West',1
INSERT dbo.RefGeographic SELECT 'MAE','My Area East',2
INSERT dbo.RefGeographic SELECT 'MAC','My Area Center',3
INSERT dbo.RefGeographic SELECT 'MAS','My Area South',4
INSERT dbo.RefGeographic SELECT 'MAN','My Area North',5
-- now you can use it in your query
SELECT t.[MAT],
t.[DVD],
t.[SALES]
FROM TestTable t
LEFT JOIN RefGeographic g
ON g.Code = t.MAT
ORDER BY DVD DESC, ISNULL(g.OrderSequence, 9999999)
You can see that a second way will allow you to change the order (if it will be ever required) without code change. Also it will work great in case if new geographic names will need to be added (eg. "My Area South-West" and "My Area Middle Of Nowhere"):hehe:
September 6, 2012 at 10:24 am
cms9651 (9/6/2012)
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
Ahh there is the challenge you were running into. You didn't have anything to order by.
Try this.
ORDER BY [DVD] DESC, CASE WHEN MAT = 'Tot' THEN 1 ELSE 0 END, CASE MAT when 'MAW' then 0 when 'MAE' then 1 when 'MAC' then 2 when 'MAS' then 4 end
yep as I was typing Eugene posted pretty much the same thing. 😀
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 6, 2012 at 10:37 am
thanks a lot for help!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply