Query Sort Order based on content

[DarkMan]

I would like to order a query based on whether or not a column has a certain value... In other words, if I have a table, Employees, with columns LastName, FirstName and StartDate, I'd like to output all columns with LastName = 'Smith', then all the rest of the columns in alphabetical order...<br>
<br>
Is this possible????<br>
<br>
I'd like one output set that would equal this..<br>
<br>
Select LastName,FirstName,StartDate from Employees where LastName='Smith' order by StartDate desc<br>
<br>
Select LastName,FirstName,StartDate from Employees where LastName != 'Smith' order by StartDate desc<br>
<br>
I've tried the union operator, but you can't use the ORDER BY clause more than once, and just putting it at the end mixes up the results.<br>
<br>
I'm sure I'm missing something quite simple, but I'm really stumped on this one... Any takers?????

 

[bitbrain]

What platform are you using? UNION sounds like the way to go. I just tried a union with two 'order bys' and it worked.<br>

 

[DarkMan]

I'm on Microsoft SQL Server 6.5... I tried the two order bys and it threw an error at me...<br>
<br>
I did figure out an interesting way to do this last night:<br>
<br>
select LastName,FirstName,StartDate,TargetName=(difference(LastName ,'Smith')/4)<br>
from Employees<br>
order by TargetName desc,LastName<br>
<br>
The difference function does a soundex comparison and returns a number 0 to 4, 4 being an exact match. Since TargetName defaults to int, I divided it by 4, so it returns a 1 for an exact match or a 0 for anything else... It works, but I would like a more elegant solution if there is one...(this one does seem to process rather quickly)<br>
<br>
Thanks in advance...

 

[bitbrain]

Cool! Sounds like a solution. Seems elegant to me if not a little cryptic. Just make sure you document it so that the next guy can maintain it!

 

[DarkMan]

Yeah.. The problem had me looking at all the functions... It would have been nice to have found a simple string=string function, but I'll take what I can get..