need advice on an SQL Stmt - 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)

need advice on an SQL Stmt

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-07 : 09:06:46

Melisa writes "Hi Guys!!

Anyway, I need to make a query wherein it retrieves a list of service centers. Pretty easy enough.. but the catch is, the company I'm doing the website on wants there accredited centers to be listed first. So the listing would look like..

Company X Center 1
Company X Center 2
ABC Company
MyCompany
XYZ

So what I did was make 2 SQL stmst...

select * from service_centers where company like 'Company X%'
select * from service_centers where company not like 'Company X%'

Is there a way to make this into 1 SQL statement. Coz the thing is because the database has a lot of records... I get a script timeout error. I fixed this by lengthening the timeout.. but it really takes a long time now to display the results.

Thanks in advance. Hoping for your quick response.

N.B. : By the way, I don't want to modify my table structure... like add a field checking if the company is accredited or not. Thanks again! I'm using SQL Server 7 running in Windows 2000 Professional Edition.
Melisa =)"

andre
Constraint Violating Yak Guru

259 Posts

Posted - 2002-03-07 : 10:08:31

You could do something like this:


select * from service_centers where company like 'Company X%'
UNION ALL
select * from service_centers where company not like 'Company X%'

MuffinMan
Posting Yak Master

107 Posts

Posted - 2002-03-07 : 10:31:48

You'll need an ORDER BY to get the data in the correct order.

select 0 as Rank, * from service_centers where company like 'Company X%'
UNION ALL
select 1 as Rank, * from service_centers where company not like 'Company X%'
ORDER BY 1 , Company

MuffinMan
Posting Yak Master

107 Posts

Posted - 2002-03-07 : 10:37:15

Also, you can do it in one statement without the UNION.

Select Rank = Case When company like 'Company X%' Then 0 Else 1 End , *
from service_centers
ORDER BY 1 , Company

melisa_uy
Starting Member

2 Posts

Posted - 2002-03-07 : 19:40:06

hey andre, muffinman!

thank you soooo much! wow... I didn't know you can use conditional statements in an sql statement. Guess I have to brush up on my sql.

till next post! =)

melisa

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources