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 10:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, August 18, 2014 8:36 AM
Points: 2,836, Visits: 5,066
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)

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")


_____________________________________________
"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 #1355467
Posted Thursday, September 6, 2012 10:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:23 PM
Points: 13,272, Visits: 12,103
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 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 #1355468
Posted Thursday, September 6, 2012 10:37 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
thanks a lot for help!
Post #1355479
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse