November 30, 2011 at 8:02 am
Sean Lange (11/30/2011)
Dev (11/30/2011)
Lowell (11/30/2011)
with Org(Business_Line)
AS
( SELECT 'Blue' UNION ALL SELECT 'Orange' UNION ALL SELECT 'Others' UNION ALL
SELECT NULL UNION ALL SELECT 'Yellow' UNION ALL SELECT 'Green' UNION ALL
SELECT 'Red' UNION ALL SELECT '' UNION ALL SELECT 'Others'
)
select Business_Line
FROM Org
Group By Business_Line
--ORDER BY ISNULL(NULLIF(Business_Line, ''), 'Others')
ORDER BY
CASE
WHEN Business_Line IS NULL
THEN 2
WHEN Business_Line =''
THEN 2
ELSE 1
END ,Business_Line
I am not sure if we can do it but as a convention, I would have moved the CASE statement in SELECT and the column alias in ORDER BY.
The difference with that approach is that it changes the data that is being selected. There is no need to select a column that is used only for sorting. The execution plan will be exactly the same.
Sorry, I didn't follow the thread & just jumped in. Agree with your point but do we have any requirements like it? I suggested it because we are familiar to see case in SELECTs rather ORDER BY. It's a kind of ease nothing else.
November 30, 2011 at 8:09 am
I tend to be very sensitive about adding columns to the output. As a web developer I tend to bind datagrids to stored proc results and let the grid build the column dynamically. As a result, if a new column is added it suddenly appears in the front end. This is great thing when business decides to add a new column, change the sproc and it is done. It can however come back to bite you in situations like this. As far as the OP here I don't think it was mentioned one way or the other. In cases where I don't have specifics about adding columns, I don't. Like I said, either way will get the data sorted correctly. One way just returns more columns which can sometimes be a bad thing when the consumer of the data is not expecting it.
_______________________________________________________________
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/
November 30, 2011 at 9:13 am
Thanks all responds, the final version is here:
select BL = ISNULL(NULLIF(Business_Line, ''), 'Others')
FROM Isc_OrgUnits
Group By Business_Line
ORDER BY
CASE
WHEN len(Business_Line)>0
THEN 1
ELSE 2
END ,Business_Line
All Business_line with null or empty will be converted to "Others" and shows in the last record of the select result.
December 1, 2011 at 6:25 am
Looks good.
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply