November 29, 2011 at 8:10 am
I've actually done this before but just can't recall how I did it! :hehe:
I have a select query, I want the result be sorted by a field only if the field is not empty, if it's empty, I want to show it as "Others" and also put the "Others" in the last result.
Can someone help me to recall how to write this query?
Thanks.
November 29, 2011 at 8:37 am
Having "Others" as the column value is a matter of a Case or IsNull/Coalesce statement in the Select clause.
In the Order By, you can put a Case statement like:
Order By Case MyColumn when '' then 2 else 1 end
Or, if you mean "null" when you say "empty":
Order By Case when MyColumn is null then 2 else 1 end
- 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
November 29, 2011 at 12:01 pm
GSquared (11/29/2011)
Having "Others" as the column value is a matter of a Case or IsNull/Coalesce statement in the Select clause.In the Order By, you can put a Case statement like:
Order By Case MyColumn when '' then 2 else 1 endOr, if you mean "null" when you say "empty":
Order By Case when MyColumn is null then 2 else 1 end
Thanks but sorry I don't get it, here is my query:
select BL = case when Business_Line is null then 'others' else Business_Line end from Org
Order By Case when Business_Line is null then Business_Line else 'others' end
The result will contains bunch of records, with some exception for this field which is null (or empty), in that case, I need to assign "Other" as the BL. For the final result, I want BL in order but with the "Other" as the last record.
I hope this is clear now.
Thanks.
November 29, 2011 at 12:12 pm
i think you just need something like this:
SELECT BL = CASE
WHEN Business_Line IS NULL
THEN 'others'
ELSE Business_Line
END
FROM Org
ORDER BY
CASE
WHEN Business_Line IS NULL
THEN 2
WHEN Business_Line =''
THEN 2
ELSE 1
END ,
Business_Line
Lowell
November 29, 2011 at 12:13 pm
GSquared (11/29/2011)
Having "Others" as the column value is a matter of a Case or IsNull/Coalesce statement in the Select clause.In the Order By, you can put a Case statement like:
Order By Case MyColumn when '' then 2 else 1 endOr, if you mean "null" when you say "empty":
Order By Case when MyColumn is null then 2 else 1 end
Order By Case when Business_Line is null then 1 else 2 end
--edit
BAH!!! It is so strange how when quoting and there is a response before you hit the quote button that is quotes the post before that one you clicked on. Such a bizarre software bug.
_______________________________________________________________
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 29, 2011 at 12:19 pm
another testable example, based on sys.objects:
SELECT name from sys.objects
order by CASE
--crap with an underscore at a certain position
--gets banished to the bottom
WHEN SUBSTRING(name,3,1) = '_'
THEN 2
ELSE 1
END,name
Lowell
November 29, 2011 at 12:26 pm
Lowell (11/29/2011)
i think you just need something like this:
SELECT BL = CASE
WHEN Business_Line IS NULL
THEN 'others'
ELSE Business_Line
END
FROM Org
ORDER BY
CASE
WHEN Business_Line IS NULL
THEN 2
WHEN Business_Line =''
THEN 2
ELSE 1
END ,
Business_Line
Thanks
I now am seeing lots of redundant records
How do I get rid of them? Apparently I can't just put distinct in the select
November 29, 2011 at 12:51 pm
In stead of using so many "case" in the select, I found this query is more elegant:
SELECT distinct ISNULL(Business_Line, 'Others')
FROM Org
ORDER BY ISNULL(Business_Line, 'Others')
The problem is, some Business_Line is empty, some is null, how do I recognize them in a simpler way?
Thanks.
November 30, 2011 at 6:28 am
You can nest NullIf inside the IsNull.
ISNULL(NULLIF(Business_Line, ''), 'Others')
That will take a blank and turn it into a Null, and then run the ISNULL on it. If there's a value other than blank, NullIf will return that, and then the IsNull will keep it.
Edit: But that, or the version you currently have, won't necessarily put the "Other" rows last in your Order By. Isn't that what you want? If so, then you need to use one of the Case versions.
- 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
November 30, 2011 at 7:16 am
GSquared (11/30/2011)
You can nest NullIf inside the IsNull.
ISNULL(NULLIF(Business_Line, ''), 'Others')That will take a blank and turn it into a Null, and then run the ISNULL on it. If there's a value other than blank, NullIf will return that, and then the IsNull will keep it.
Thanks for explaining that
Edit: But that, or the version you currently have, won't necessarily put the "Other" rows last in your Order By. Isn't that what you want? If so, then you need to use one of the Case versions.
Yes, I can't get it working, can you teach me write one?
Thanks.
November 30, 2011 at 7:19 am
Lowell, Gus and I all gave you an example of how to do that.
order by case when...
_______________________________________________________________
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 7:35 am
Sean Lange (11/30/2011)
Lowell, Gus and I all gave you an example of how to do that.
order by case when...
I know, I tried to adopt it like here, it doesn't work out, maybe my code has problem:
select distinct Business_Line
FROM Org
--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
It throws error:
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
November 30, 2011 at 7:41 am
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
Lowell
November 30, 2011 at 7:49 am
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.
November 30, 2011 at 7:57 am
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.
_______________________________________________________________
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/
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply