T-SQL question - 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

Transact-SQL (2000)

T-SQL question

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-01-12 : 09:00:28

Mike writes "Here's a t-sql question:

use pubs
GO

select * from authors
order by
case substring(au_lname, 1,1) when 'B' then 99999
else 1
end

Can anyone explain how this works, seeing as there are significantly < 99999 columns in the authors table. It seems that it's a way to get the authors whose last name start with 'B' to the bottom of the list.

Thanks in advance"

Kristen
Test

22859 Posts

Posted - 2006-01-12 : 14:25:54

Hi Mike, Welcome to SQL Team!

Well, actually the sorting is random.

Yes authors who's names begin with "B" will be last, but the order of the ones begining with "B", within that final section, is not defined, nor is the order of the first lot!

More commonly this type of requirement is handled using something like:


select * from authors
order by
case substring(au_lname, 1,1) 
    when 'B' 
        then 99999
        else 1
    end,
au_lname

where some extra data is used to provide a "first level sort", and then some more detail for the tie-break.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources