Sorting a query based on a column's value 1

[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?????

 

[DougP]

If I understand what you want.<br>
What about this<br>
<br>
ORDER BY LASTNAME, FIRSTNAME, STARTDATE ...<br>
this will put all of the Smiths in order then all of the Marys next then all of the Mary Smiths who started on 1/1/00 next<br>
<br>
Now you realize that a Record or row is one item<br>
you can't switch them around <br>
Like this example here are 2 records or rows<br>
<br>
lastname firstname Startdate<br>
Smith Mary 3/1/00 &lt;this is one row<br>
Jones Samuel 2/2/00 &lt;this is 2nd row<br>
<br>
you can't get these results sorting lastname and firstname etc<br>
Jones Mary 2/2/00<br>
Smith Samuel 3/1/00<br>
<br>
Mary has to stay with Smith and 3/1/00<br>
<br>

 

[calahans]

Hi Darkman,<br>
<br>
I haven't tried this but what about something like,<br>
<br>
select a.* from <br>
(<br>
Select LastName,FirstName,StartDate from Employees where LastName='Smith' ) a order by a.StartDate desc select b.* from <br>
(Select LastName,FirstName,StartDate from Employees where LastName != 'Smith' ) b order by b.StartDate desc<br>
<br>
It can be run as one statement<br>
<br>
C

 

[tcorrigan]

Here's the solution:<br>
<br>
create table #XNames (lastname char(30), firstname char(15))<br>
<br>
insert into<br>
#XNames<br>
select <br>
lastname,<br>
firstname<br>
from<br>
employee<br>
where <br>
lastname = 'Smith'<br>
order by<br>
lastname,<br>
firstname<br>
<br>
insert into<br>
#XNames<br>
select <br>
lastname,<br>
firstname<br>
from<br>
employee<br>
where <br>
lastname != 'Smith'<br>
order by<br>
lastname,<br>
firstname<br>
<br>
select * from #XNames<br>
<br>
drop table #XNames<br>

 

[DarkMan]

tcorrigan - Thanks! That's exactly what I was looking for. I did come up with another solution for this:<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 returns a pattern match of 0-4, four being an exact match. I simply divided the result by four so any result less than one rounds down to zero giving me a match/no match situation. I can then order by whether I had a match or not...<br>
<br>
I'll check both solutions to see which is faster and use it. Thanks again.<br>
<br>
<br>
calahans - <br>
<br>
This works great, but still gives me two result sets, which complicates my output... Thanks though...<br>
<br>
<br>
Thanks again guys.