×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Sorting a query based on a column's value

Sorting a query based on a column's value

Sorting a query based on a column's value

(OP)
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...

Is this possible????

I'd like one output set that would equal this..

Select LastName,FirstName,StartDate from Employees where LastName='Smith' order by StartDate desc

Select LastName,FirstName,StartDate from Employees where LastName != 'Smith' order by StartDate desc

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.

I'm sure I'm missing something quite simple, but I'm really stumped on this one... Any takers?????

RE: Sorting a query based on a column's value

If I understand what you want.
What about this

ORDER BY LASTNAME, FIRSTNAME, STARTDATE ...
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

Now you realize that a Record or row is one item
you can't switch them around
Like this example here are 2 records or rows

lastname firstname Startdate
Smith Mary 3/1/00 <this is one row
Jones Samuel 2/2/00 <this is 2nd row

you can't get these results sorting lastname and firstname etc
Jones Mary 2/2/00
Smith Samuel 3/1/00

Mary has to stay with Smith and 3/1/00

RE: Sorting a query based on a column's value

Hi Darkman,

I haven't tried this but what about something like,

select a.* from
(
Select LastName,FirstName,StartDate from Employees where LastName='Smith' ) a order by a.StartDate desc select b.* from
(Select LastName,FirstName,StartDate from Employees where LastName != 'Smith' ) b order by b.StartDate desc

It can be run as one statement

C

RE: Sorting a query based on a column's value

Here's the solution:

create table #XNames (lastname char(30), firstname char(15))

insert into
#XNames
select
lastname,
firstname
from
employee
where
lastname = 'Smith'
order by
lastname,
firstname

insert into
#XNames
select
lastname,
firstname
from
employee
where
lastname != 'Smith'
order by
lastname,
firstname

select * from #XNames

drop table #XNames

RE: Sorting a query based on a column's value

(OP)
tcorrigan - Thanks! That's exactly what I was looking for. I did come up with another solution for this:

select LastName,FirstName,StartDate,TargetName=(difference(LastName ,'Smith')/4)
from Employees
order by TargetName desc,LastName

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...

I'll check both solutions to see which is faster and use it. Thanks again.


calahans -

This works great, but still gives me two result sets, which complicates my output... Thanks though...:-)


Thanks again guys.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close