order by problem - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

order by problem

Author

Topic

PeterG
Posting Yak Master

156 Posts

Posted - 2003-02-18 : 14:00:34

I have the following query to sort my recordset with null company name at the bottom of the list, but the query doesn't work. I still get those with null values at the top of the list.

"Select * FROM SurveyData
ORDER BY ISNULL(CompName,"ZZZZZZ"), DateSubmitted"

How should I fix this to show what I want?

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-18 : 15:09:54

Don't Know Why...The Following sql when executed:

select * from table1
order by col2
go
Select * from Table1
Order By IsNull(col2,'zzzzzzzz')
go

Returns:

Col1 col2 col3 col4
----------- ---- ------------------------------------------------------ ----------
NULL NULL 2000-01-01 00:00:00.000 NULL
NULL NULL 2002-12-19 10:59:57.360 NULL
NULL NULL 1900-01-02 00:00:00.000 NULL
NULL NULL 1900-01-01 00:00:00.000 NULL
NULL NULL NULL 01-01-2000
NULL NULL 2000-01-01 00:00:00.000 NULL
1 A NULL NULL
2 B NULL NULL
3 C NULL NULL

(9 row(s) affected)

Col1 col2 col3 col4
----------- ---- ------------------------------------------------------ ----------
1 A NULL NULL
2 B NULL NULL
3 C NULL NULL
NULL NULL 2000-01-01 00:00:00.000 NULL
NULL NULL 2002-12-19 10:59:57.360 NULL
NULL NULL 1900-01-02 00:00:00.000 NULL
NULL NULL 1900-01-01 00:00:00.000 NULL
NULL NULL NULL 01-01-2000
NULL NULL 2000-01-01 00:00:00.000 NULL

(9 row(s) affected)

PeterG
Posting Yak Master

156 Posts

Posted - 2003-02-18 : 15:38:53

I looked at the table again and realize that the column (compname) is empty (not a NULL value). I think this is why the ISNULL function doesn't work. How can I rewrite my query now that I know that this column has an empty string value?

nr
SQLTeam MVY

12543 Posts

Posted - 2003-02-18 : 15:42:26

Select * FROM SurveyData
ORDER BY case when CompName = '' then 1 else 0 end, CompName, DateSubmitted

if you want to cater for null as well

Select * FROM SurveyData
ORDER BY case when coalesce(CompName,'') = '' then 1 else 0 end, CompName, DateSubmitted

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-18 : 15:50:54

How About:

SELECT *
FROM SurveyData
ORDER BY CASE WHEN CompName = '' Then 'ZZZZZZ' Else CompName End
, DateSubmitted

Hope this helps

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources