SQL Search Query String

I have a project that just had the parameters changed.  It used to be that an employee logs in and see's the work that's assigned to them only.  Now the employee is supposed to sign in and see their job assignments first and then other job assignments.  In the old days the command was select * from table where employee = 'john'

Results:
Name:   Job Stop:
John      123 Main St
John      876 Oak Ave
John      345 Maple Road

This was fine because the person in charge of operations just wanted names and the order of the database which was imported from an Excel spreadsheet to remain the same.  Now from a logic perspective, I'm not quite sure how to do a:
select * from table where [employee] = 'john' -- list these first in no order other than how originally in database and then list all the others
I guess I could be sloppy and have a 2nd select there such as:
select * from table where not [employee] = 'john'

Results:
Name:   Job Stop:
John      123 Main St
John      876 Oak Ave
John      345 Maple Road
Tom      643 Birch St
Tom      222 Rose Ave
Bob      909 Sky Drive
Josh      877 Park Ave

Thanks in advance...
Erika KoelleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NerdsOfTechTechnology ScientistCommented:
 select * from table where [employee] = 'john'
 UNION ALL
 select * from table where NOT [employee] = 'john'

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NerdsOfTechTechnology ScientistCommented:
You can order by criteria as well by making the above into a subquery:

 SELECT * FROM
 (
 select *, 0 AS oflag from table where [employee] = 'john'
 UNION ALL
 select *, 1 AS oflag from table where NOT [employee] = 'john'
 ) t1
 ORDER BY oflag, employee

Open in new window

0
Vaibhav GoelMSBI , SQL ConsultantCommented:
Hello Erika Koelle

We need to use WHERE clause like below -

select * from table where [employee] <> 'john'

OR

select * from table where [employee] != 'john'
0
Vaibhav GoelMSBI , SQL ConsultantCommented:
Hello Erika Koelle

If you want all the John records first then you can use below. In this case you do not have to use any where clause.

select * from table
ORDER BY CASE WHEN [employee] = 'john' THEN 1 ELSE 2 END , [employee]

Vaibhav
1
Erika KoelleAuthor Commented:
This works perfectly and has already been added...thanks.

 select * from table where [employee] = 'john'
 UNION ALL
 select * from table where NOT [employee] = 'john'
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.