Changing a null to another value in select?

Hi all,
I have a result set coming back with one item that is null, because I am
trying to sort by this column this result is always at the top of the result
set, however I need them to be at the bottom of the result set, I cant
change the ordering because then the other results will no longer be
alphabetically sorted. Is there a way to change a 'null' to something else
if it is returned in a result set? My hope was perhaps to set it to be 10
Z's (ie, zzzzzzzzzz) so that this would always (unless a REAL oddity
happens) be at the bottom?
Possible?
Regards
RobSelect isnull(mycolumn, 'zzzzzzzzzz') from MyTable
Hope this helps
Chris
"Rob Meade" <robb.meade@.NO-SPAM.kingswoodweb.net> wrote in message
news:4BbJd.17892$GG1.5654@.text.news.blueyonder.co.uk...
> Hi all,
> I have a result set coming back with one item that is null, because I am
> trying to sort by this column this result is always at the top of the
> result
> set, however I need them to be at the bottom of the result set, I cant
> change the ordering because then the other results will no longer be
> alphabetically sorted. Is there a way to change a 'null' to something
> else
> if it is returned in a result set? My hope was perhaps to set it to be 10
> Z's (ie, zzzzzzzzzz) so that this would always (unless a REAL oddity
> happens) be at the bottom?
> Possible?
> Regards
> Rob
>|||"Chris, Master of All Things Insignificant" wrote ...

> Select isnull(mycolumn, 'zzzzzzzzzz') from MyTable
Hi Chris,
Thanks for the reply. I managed something similar myself, here's my view:
SELECT '1' AS ViewOrder1, '1' AS ViewOrder2, a.PageID, a.NGID,
a.PageName, a.PageKeywords, a.PageDisplayOrder, a.PageFooterDisplayOrder,
a.PageContent, a.PageIsLive, a.PageIsDefault,
a.PageIsStatic, a.PageIsExternal, a.PageIsKB, a.PageIsSearchResults,
a.PageIsNews,
a.PageIsPromoted, a.PageIsExcludedFromNavigation,
a.PageIsIncludedInFooter, a.PageURL, a.DisplayPrint, a.DisplayEmail,
a.DateCreated,
a.DateLastUpdated, b.NGName, b.NGDescription,
b.NGDisplayOrder, b.SectionID, c.SectionName, c.SectionDesc,
c.SectionDisplayOrder,
c.SectionPlacement, d .WebsiteID, d .WebsiteName,
e.Forename + ' ' + e.Surname AS PageCreatedBy,
f.Forename + ' ' + f.Surname AS PageLastUpdatedBy
FROM tblPages AS a LEFT JOIN
tblNavigationGroups AS b ON a.NGID = b.NGID LEFT JOIN
tblSections AS c ON b.SectionID = c.SectionID LEFT
JOIN
tblWebsites AS d ON c.WebsiteID = d .WebsiteID LEFT
JOIN
tblUsers AS e ON a.CreatedBy = e.UserID LEFT JOIN
tblUsers AS f ON a.LastUpdatedBy = f.UserID
WHERE b.SectionID IS NOT NULL AND b.SectionID <> '0' AND a.NGID <> '0'
AND b.NGIsNoGroup = '0'
UNION ALL
SELECT '1' AS ViewOrder1, '2' AS ViewOrder2, a.PageID, a.NGID,
a.PageName, a.PageKeywords, a.PageDisplayOrder, a.PageFooterDisplayOrder,
a.PageContent, a.PageIsLive, a.PageIsDefault,
a.PageIsStatic, a.PageIsExternal, a.PageIsKB, a.PageIsSearchResults,
a.PageIsNews,
a.PageIsPromoted, a.PageIsExcludedFromNavigation,
a.PageIsIncludedInFooter, a.PageURL, a.DisplayPrint, a.DisplayEmail,
a.DateCreated,
a.DateLastUpdated, b.NGName, b.NGDescription,
b.NGDisplayOrder, b.SectionID, c.SectionName, c.SectionDesc,
c.SectionDisplayOrder,
c.SectionPlacement, d .WebsiteID, d .WebsiteName,
e.Forename + ' ' + e.Surname AS PageCreatedBy,
f.Forename + ' ' + f.Surname AS PageLastUpdatedBy
FROM tblPages AS a LEFT JOIN
tblNavigationGroups AS b ON a.NGID = b.NGID LEFT JOIN
tblSections AS c ON b.SectionID = c.SectionID LEFT
JOIN
tblWebsites AS d ON c.WebsiteID = d .WebsiteID LEFT
JOIN
tblUsers AS e ON a.CreatedBy = e.UserID LEFT JOIN
tblUsers AS f ON a.LastUpdatedBy = f.UserID
WHERE b.SectionID IS NOT NULL AND b.SectionID <> '0' AND a.NGID <> '0'
AND b.NGIsNoGroup = '1'
UNION ALL
SELECT '3' AS ViewOrder1, '1' AS ViewOrder2, a.PageID, a.NGID,
a.PageName, a.PageKeywords, a.PageDisplayOrder, a.PageFooterDisplayOrder,
a.PageContent, a.PageIsLive, a.PageIsDefault,
a.PageIsStatic, a.PageIsExternal, a.PageIsKB, a.PageIsSearchResults,
a.PageIsNews,
a.PageIsPromoted, a.PageIsExcludedFromNavigation,
a.PageIsIncludedInFooter, a.PageURL, a.DisplayPrint, a.DisplayEmail,
a.DateCreated,
a.DateLastUpdated, b.NGName, b.NGDescription,
b.NGDisplayOrder, b.SectionID, 'ZZZZZZZZZZ', c.SectionDesc,
c.SectionDisplayOrder,
c.SectionPlacement, d .WebsiteID, d .WebsiteName,
e.Forename + ' ' + e.Surname AS PageCreatedBy,
f.Forename + ' ' + f.Surname AS PageLastUpdatedBy
FROM tblPages AS a LEFT JOIN
tblNavigationGroups AS b ON a.NGID = b.NGID LEFT JOIN
tblSections AS c ON b.SectionID = c.SectionID LEFT
JOIN
tblWebsites AS d ON c.WebsiteID = d .WebsiteID LEFT
JOIN
tblUsers AS e ON a.CreatedBy = e.UserID LEFT JOIN
tblUsers AS f ON a.LastUpdatedBy = f.UserID
WHERE (b.SectionID IS NULL OR
b.SectionID = '0') AND a.NGID <> '0'
UNION ALL
SELECT '4' AS ViewOrder1, '1' AS ViewOrder2, a.PageID, a.NGID,
a.PageName, a.PageKeywords, a.PageDisplayOrder, a.PageFooterDisplayOrder,
a.PageContent, a.PageIsLive, a.PageIsDefault,
a.PageIsStatic, a.PageIsExternal, a.PageIsKB, a.PageIsSearchResults,
a.PageIsNews,
a.PageIsPromoted, a.PageIsExcludedFromNavigation,
a.PageIsIncludedInFooter, a.PageURL, a.DisplayPrint, a.DisplayEmail,
a.DateCreated,
a.DateLastUpdated, b.NGName, b.NGDescription,
b.NGDisplayOrder, b.SectionID, 'ZZZZZZZZZZ', c.SectionDesc,
c.SectionDisplayOrder,
c.SectionPlacement, d .WebsiteID, d .WebsiteName,
e.Forename + ' ' + e.Surname AS PageCreatedBy,
f.Forename + ' ' + f.Surname AS PageLastUpdatedBy
FROM tblPages AS a LEFT JOIN
tblNavigationGroups AS b ON a.NGID = b.NGID LEFT JOIN
tblSections AS c ON b.SectionID = c.SectionID LEFT
JOIN
tblWebsites AS d ON c.WebsiteID = d .WebsiteID LEFT
JOIN
tblUsers AS e ON a.CreatedBy = e.UserID LEFT JOIN
tblUsers AS f ON a.LastUpdatedBy = f.UserID
WHERE (b.SectionID IS NULL OR
b.SectionID = '0') AND a.NGID = '0'
As you can see the last 2 sections of this (where viewOrder1 is either 3 or
4 etc) I have inserted the 'ZZZZZZZZZZ' as the section name...
This does work, but feels a bit flakey...if someone was to actually create a
'section' in my application called 'ZZZZZZZZZZZ' it would obviously cause
problems...
I originally had in my ASP code 4 statements which went off and got the
respective results for each case, but I thought making just one hit would be
better, now I face a different problem...
Any ideas anyone?
Regards
Rob|||Use a CASE expression in the ORDER BY clause.
Example:
use northwind
go
select
customerid,
companyname,
region
from
customers
order by
case when region is null then 1 else 0 end asc,
customerid
go
AMB
"Rob Meade" wrote:

> Hi all,
> I have a result set coming back with one item that is null, because I am
> trying to sort by this column this result is always at the top of the resu
lt
> set, however I need them to be at the bottom of the result set, I cant
> change the ordering because then the other results will no longer be
> alphabetically sorted. Is there a way to change a 'null' to something els
e
> if it is returned in a result set? My hope was perhaps to set it to be 10
> Z's (ie, zzzzzzzzzz) so that this would always (unless a REAL oddity
> happens) be at the bottom?
> Possible?
> Regards
> Rob
>
>|||"Alejandro Mesa" wrote ...

> Use a CASE expression in the ORDER BY clause.

> use northwind
(wishing now I'd not deleted that recently)

> order by
> case when region is null then 1 else 0 end asc,
> customerid
That looks like what I want, I think, I cant quite work out the 1 / 0 thing
though - whats that actually going to do - in words etc...
ie, does that make region = 1 if it = null, and = 0 if its not null?
Thanks for the reply.
Regards
Rob|||"Alejandro Mesa" wrote ...

> Use a CASE expression in the ORDER BY clause.
>
[snip]
Hi Alejandro,
I was unable to get it to work in the ORDER BY clause, but your reply did
make me go and investigate the use of CASE expressions, I've now popped it
in the SELECT statement and its working perfectly.
Thank you.
Regards
Rob|||Rob,
ORDER BY
CASE WHEN itemColumn IS NULL THEN 1 ELSE 0 END,
itemColumn
Steve Kass
Drew University
Rob Meade wrote:

>Hi all,
>I have a result set coming back with one item that is null, because I am
>trying to sort by this column this result is always at the top of the resul
t
>set, however I need them to be at the bottom of the result set, I cant
>change the ordering because then the other results will no longer be
>alphabetically sorted. Is there a way to change a 'null' to something else
>if it is returned in a result set? My hope was perhaps to set it to be 10
>Z's (ie, zzzzzzzzzz) so that this would always (unless a REAL oddity
>happens) be at the bottom?
>Possible?
>Regards
>Rob
>
>|||"Steve Kass" wrote ...

> ORDER BY
> CASE WHEN itemColumn IS NULL THEN 1 ELSE 0 END,
> itemColumn
Hi Steve,
Could you explain to me what the 1 and 0 are/do please?
Regards
Rob|||Imagine that you put this in the SELECT list instead:
SELECT ... CASE WHEN itemColumn IS NULL THEN 1 ELSE 0 END AS sortcol
For a row with NULL in itemColumn, the expression will return the value 1. I
f there is a non-null
value in itemColumn, the expression will return the value 0. So, say you hav
e some rows (sortcol is
the one produced by above expression)
ItemColumn SortCol
23 0
NULL 1
1 0
NULL 1
Now, imagine that you do:
ORDER BY SortCol, ItemColumn
Get it?
The "short cut" Steve did is to put the expression in the ORDER BY clause in
stead of in the SELECT
clause. Logically, the columns didn't "materialize" before the sort operatio
n...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Rob Meade" <robb.meade@.NO-SPAM.kingswoodweb.net> wrote in message
news:8roJd.18310$GG1.8683@.text.news.blueyonder.co.uk...
> "Steve Kass" wrote ...
>
> Hi Steve,
> Could you explain to me what the 1 and 0 are/do please?
> Regards
> Rob
>|||"Tibor Karaszi" wrote ...

> ItemColumn SortCol
> 23 0
> NULL 1
> 1 0
> NULL 1
> Now, imagine that you do:
> ORDER BY SortCol, ItemColumn
> Get it?
> The "short cut" Steve did is to put the expression in the ORDER BY clause
instead of in the SELECT
> clause. Logically, the columns didn't "materialize" before the sort
operation...
Hi Tibor,
Thanks for your reply...
I get it now, the 1 / 0 were for the ordering etc, as where I was using
1,2,3,4 previously...When I first looked at it I was wondering whether they
could only 'ever' be those values or something - its not something I've come
across/used before..
Thanks for the explanation.
Regards
Rob