Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Silly Order By Question...

Author  Topic 

JBelthoff
Posting Yak Master

173 Posts
Posted - 2005-12-13 : 17:09:40
I have a table that contains a users FirstNAme, LastName and sometimes CompanyNAme. When the Users display I would like to Order By LastNAme when there is no CompanyName and CompnayName if there is one.

I'm not sure I said that right so I have included tables, data and results I want below.

Any help would be appreciated.

Thanks,
DDL And Data ----

CREATE TABLE [dbo].[Acccounts] (
	[aid] [int] IDENTITY (250, 1) NOT NULL ,
	[FName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[LName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CompanyName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

Insert Acccounts ( FName, LName, CompanyName )
Values ( 'John', 'Doe', '' )

Insert Acccounts ( FName, LName, CompanyName )
Values ( 'John', 'Zak', 'ABC Company' )

Insert Acccounts ( FName, LName, CompanyName )
Values ( 'John', 'Toranado', '' )

Insert Acccounts ( FName, LName, CompanyName )
Values ( 'John', 'Willimas', '' )

Insert Acccounts ( FName, LName, CompanyName )
Values ( 'John', 'Yak', 'RRG Company' )

Insert Acccounts ( FName, LName, CompanyName )
Values ( 'John', 'Domains', '' )
GO

** Results Wanted **

aid         FName                     LName                     CompanyName               
----------- ------------------------- ------------------------- ------------------------- 
251         John                      Zak                       ABC Company
250         John                      Doe                       
255         John                      Domains                   
254         John                      Yak                       RRG Company
252         John                      Toranado                  
253         John                      Willimas

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2005-12-13 : 17:22:44
I'm almost crying, someone actually provided all the info needed to help. Bwaaaaa! Thank You!!

select	aid
	,FName
	,LName
	,CompanyName               
from	Acccounts
order by isNull(nullif(companyName,''), LName)


btw, why empty strings instead of NULLs for companyName?

Be One with the Optimizer
TG
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2005-12-13 : 17:43:10
>> btw, why empty strings instead of NULLs for companyName?
...refraining from comments... 
select
	aid
	,FName
	,LName
	,CompanyName               
from	Acccounts
order by case companyName when '' then LName else companyName end


rockmoose
Go to Top of Page

JBelthoff
Posting Yak Master

173 Posts
Posted - 2005-12-13 : 17:58:50
Thanks guys that worked great!

Just curious, are empty strings "bad" as opposed to nulls?
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2005-12-14 : 05:20:18
No, NULLS should always be avoided.

rockmoose
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-12-14 : 07:33:24
"are empty strings "bad" as opposed to nulls?"

Depends. Is "empty" an appropriate value for the column, or does it mean "Don't know"? If it means "don't know" then I think the column value should be NULL. That way it will FAIL any comparison tests, which given you don't know the value seems appropriate to me!

Address -> Empty -> Implies to me "homeless"

Address -> NULL -> Implies to me "We don't, yet, know, but we might in the future"

Kristen
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2005-12-14 : 07:44:05
quote:

>> btw, why empty strings instead of NULLs for companyName?
...refraining from comments...


quote:
No, NULLS should always be avoided.

Not much will power RockMan...Good to see you back!

My opinion is either design the model so Nulls can be avoided or use nulls. Empty strings are as meaningless as nulls but take up space as well as you need lays some coding traps (well I guess NULLs do that too).

Be One with the Optimizer
TG
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2005-12-14 : 09:09:56
quote:
Not much will power RockMan...Good to see you back!

Hi TG!, yeah my willpower was null.

quote:
design the model so Nulls can be avoided

Amen to that!
null is a denormalized outer join.

The CompanyName column could a) reference a table with valid companies, or b) allow "free text" data entry.
With the current design (i.e. we can enter Accounts where the companyName is "unknown", "n/a", "none of your business",,,,)
a) we accept '', 'n/a', 'undisclosed' as acceptable values in the company domain.
b) if we accept nulls I am of the opinion that we create a design that demands coding traps and special handling of nulls, by design.

Belthoff, welcome to the never-ending null debate

_____________________
SET ANSI_NULLS OFF
I have null problems

rockmoose
Go to Top of Page

druer
Constraint Violating Yak Guru

314 Posts
Posted - 2005-12-14 : 09:43:45
Shakespeare even wrote about this debate hundreds of years ago "Much ado about nothing."
Go to Top of Page

JBelthoff
Posting Yak Master

173 Posts
Posted - 2005-12-14 : 09:51:58
quote:
Belthoff, welcome to the never-ending null debate


Thank you, very nice to be here.

In my particular design, users can be listed by their Company Name if there is one and if not they are listed by their Fist and Last Names.

So if a user decides to enter 'None of your damn business' than that is what will be displayed. His/Her choice.
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-12-14 : 10:00:45
"welcome to the never-ending null debate"

Make up your mind - is there, or is there not, a debate?

Kristen
Go to Top of Page

JBelthoff
Posting Yak Master

173 Posts
Posted - 2005-12-14 : 10:11:23
I look at it this way,

Im my app it takes ten [10] keystrokes to type IsdbNull()

Yet it only takes five [5] to type <> ""

Should there be any other criteria? Haha!

I just increased my efficiency by 100% using empty strings
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-12-14 : 10:21:48
"Yet it only takes five [5] to type <> """

but unfortunately that will fail if there are any NULL values ... so either the column needs to be NOT NULL or you may very need:

type <> "" AND type IS NOT NULL

or some unambiguous variation along those lines

Kristen
Go to Top of Page

JBelthoff
Posting Yak Master

173 Posts
Posted - 2005-12-14 : 10:37:15
So you are telling me that Homer Simpson logic doesn't apply to MS SQL Server.
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-12-14 : 11:07:01
I don't reckon the Debbie that Did Dallas is going to be much help either!
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2005-12-14 : 14:02:18
Feel the urge to convert empty strings to null....

____________________
Tempted, you will be

rockmoose
Go to Top of Page

Goudinov
Starting Member

14 Posts
Posted - 2005-12-14 : 16:26:40
So... you anti-null'ers out there... do you define defaults for every column on every table?
Go to Top of Page

jhermiz

3564 Posts
Posted - 2005-12-14 : 16:28:13
quote:
Originally posted by Goudinov

So... you anti-null'ers out there... do you define defaults for every column on every table?


I'm not anti null, nor am I for null..I'm not sure where I stand :), but I try to avoid them as much as possible.

Yes a default


Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2005-12-14 : 16:33:38
quote:
Originally posted by Goudinov

So... you anti-null'ers out there... do you define defaults for every column on every table?


just to be picky:
defaults don't prevent NULLs. NOT NULL property prevents NULLs

Be One with the Optimizer
TG
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2005-12-14 : 17:41:36
quote:
Originally posted by Goudinov

So... you anti-null'ers out there... do you define defaults for every column on every table?

Do you feel that a not null column requires a default?
That is not the case.

rockmoose
Go to Top of Page

JBelthoff
Posting Yak Master

173 Posts
Posted - 2005-12-14 : 18:01:53
I'm not anti-null -- I'm pro empty string. This is a great debate isn't it?

Now I see why you guys have 250000000 post's each! Haha!
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site