A Special Thank you

I want to thank Brett Kaiser and Pat Phelan for advising me to get the "The Guru's Guide to Transcact-SQL" HOLY MOLY what an awesome book and the software that comes with it is so awesome "Sequin". This book is better then any of the Microsoft books, it breaks it all down for you and doesnt make it too difficult to understand I stayed up late last night reading this book. (which is why I'm exhausted today). This is by far the best Transact-SQL book I have ever come across.

THANKS GUYS :):):)I'm Blushing...

:p

You should also pick up ADMIN911 for SQL Server 2000 by Brian Knight

It's more of an administrators guide, but it's got lots of good stuff about security, performance, ect...mine is very dog eared|||Hey Brett...can I have your copy? ;) Apparently it's outta print, and the dude that has his listed for $80 on Amazon wants his copy more than I do... :rolleyes:

I wonder if they'll come out with another version...have you (or anyone else) heard such a thing?|||I'd really rather have Brian's SQL Server 2000 for Experienceed DBAs (http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=6921cRQAhW&isbn=0072227885&itm=3) instead. I think that it is more current, and includes additional material.

-PatP|||THANKS GUYS :):):)Great zot! A lady that gets giddy reading tech. Why the heck couldn't I find any girls like that ?!?!

Then again, I shouldn't really complain... My girlfriend is nearly as geeky as I am, just in different areas (she does infrastructure and Citrix).

-PatP|||Just added both to my bookshelf on safari.|||I'd really rather have Brian's SQL Server 2000 for Experienceed DBAs (http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=6921cRQAhW&isbn=0072227885&itm=3) instead. I think that it is more current, and includes additional material.

-PatP

Looks good...why do you say more current though...|||My first reason for thinking SQL Server 2000 for Experienceed DBAs is more current than Admin911: SQL Server 2000 is some of Brian's own comments about it... I didn't have any reason to question his comment.

-PatP|||Such as? At the library right now...damn if they don't have any tech books...

And I'd just as soon own my own copy...

Does Brian Speak?

A Special Order By clause?

The following SELECT query gives me a list of 50 plus countries. How do I order them by 'United States' First (happens to be ID 225) and then alphabetical?

SELECT Country_ID, Country_Long FROM Countries WHERE isIndustrial = 1
ORDER BY Country_Long

I think this will work with you:

 
1SELECT 1,-- to keep a default value of county always on top (don't read it in your application)2 225,-- the value for U.S.3'United States'-- the default country (U.S. for example)45union67 SELECT 2,-- we need it to grantee the default value will be always up (you may use any value > 1)8 Country_ID,9 Country_Long10FROM Countries11WHERE isIndustrial = 112AND Country_ID <> 225-- Country_ID for the default county (in the first SELECT)13ORDER BY 1-- used to keep the default contry always in the top (first returned record)1415 GO
 

Good luck.

|||

You probably need to do 2 SELECTs and do a UNION. The first one will have 'United States' followed by the rest in alphabetical order.

|||

I read the question wrong

What CS4Ever and ndinakar both have posted is how you want to do this.

|||

Thanks all!!

Look at what this other awesome programmer came up with in addition to the winning example here:

SELECT * FROM COUNTRIES order by case country_long when 'United States' then 1 else 2 end asc, country_long

|||

SolitaryMan:

Thanks all!!

Look at what this other awesome programmer came up with in addition to the winning example here:

SELECT * FROM COUNTRIES order by case country_long when 'United States' then 1 else 2 end asc, country_long

Your are welcome SolitaryMan.

Thanks for posting another example. Actullay there is always more than a way to do the thing.

I believe (not sure) the example in my previous post in better in term of performance, because of the CASE in the ORDER BY clause for the example you menioned. Since database engine will check the case of each record (long processing) while this is not the case in my example.

Again, thanks for sharing the example.

|||

CS4Ever:

I think this will work with you:

 
1SELECT 1,-- to keep a default value of county always on top (don't read it in your application)2 225,-- the value for U.S.3'United States'-- the default country (U.S. for example)45union67 SELECT 2,-- we need it to grantee the default value will be always up (you may use any value > 1)8 Country_ID,9 Country_Long10FROM Countries11WHERE isIndustrial = 112AND Country_ID <> 225-- Country_ID for the default county (in the first SELECT)13ORDER BY 1-- used to keep the default contry always in the top (first returned record)1415 GO
 

Good luck.

Change the order by clause to:

1ORDER BY 1, Country_Long

Good luck.