Order by with specific record on top

I have a routine that is displays my record in date order.
I want to be able to put a specific record from that set of rows on top,
with the rest in date order (as it is now, but without the one I moved to
the top.
For example, I have the following select:
Select JobID,
jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
',''))
from ApplicantResume a
join Position p on (a.PositionID = p.PositionID)
where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
and
Email = 'tscheid@.yahoo.com'
order by datesubmitted desc
This gives me the following:
JobID jobTitle
_______ ________________________________________
__
3734 Desktop Computer Support Technician/22Feb05
3733 Desktop Computer Support Technician/21Feb05
3732 Desktop Computer Support Technician/19Feb05
3731 Desktop Computer Support Technician/17Feb05
3730 Desktop Computer Support Technician/15Feb05
3729 Desktop Computer Support Technician/14Feb05
3728 Desktop Computer Support Technician/12Feb05
What I want to do is take Job ID 3731 and move it to the top like so:
JobID jobTitle
_______ ________________________________________
__
3731 Desktop Computer Support Technician/17Feb05
3734 Desktop Computer Support Technician/22Feb05
3733 Desktop Computer Support Technician/21Feb05
3732 Desktop Computer Support Technician/19Feb05
3730 Desktop Computer Support Technician/15Feb05
3729 Desktop Computer Support Technician/14Feb05
3728 Desktop Computer Support Technician/12Feb05
I tried using a union ( I am sure there is a better way):
Select JobID,
jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
',''))
from ApplicantResume a
join Position p on (a.PositionID = p.PositionID)
where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
and
Email = 'tscheid@.yahoo.com' and
JobID = 3731
union
Select JobID,
jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
',''))
from ApplicantResume a
join Position p on (a.PositionID = p.PositionID)
where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
and
Email = 'tscheid@.yahoo.com' and
JobID <> 3731
order by datesubmitted desc
I got an error:
Server: Msg 104, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if the statement contains a
UNION operator.
I put datesubmitted in the select statment and got the following:
JobID jobTitle
_______ ________________________________________
__
3734 Desktop Computer Support Technician/22Feb05
3733 Desktop Computer Support Technician/21Feb05
3732 Desktop Computer Support Technician/19Feb05
3731 Desktop Computer Support Technician/17Feb05
3730 Desktop Computer Support Technician/15Feb05
3729 Desktop Computer Support Technician/14Feb05
3728 Desktop Computer Support Technician/12Feb05
I assume it did the union and then order by, which negated what I was trying
to do.
How do I do the order by, just on the 2nd select and have it put the 2
together?
Thanks,
Tom"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OEK6BxSGFHA.2296@.TK2MSFTNGP15.phx.gbl...
>I have a routine that is displays my record in date order.
> I want to be able to put a specific record from that set of rows on top,
> with the rest in date order (as it is now, but without the one I moved to
> the top.
> For example, I have the following select:
> Select JobID,
> jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
> ',''))
> from ApplicantResume a
> join Position p on (a.PositionID = p.PositionID)
> where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
> and
> Email = 'tscheid@.yahoo.com'
> order by datesubmitted desc
> This gives me the following:
> JobID jobTitle
> _______ ________________________________________
__
> 3734 Desktop Computer Support Technician/22Feb05
> 3733 Desktop Computer Support Technician/21Feb05
> 3732 Desktop Computer Support Technician/19Feb05
> 3731 Desktop Computer Support Technician/17Feb05
> 3730 Desktop Computer Support Technician/15Feb05
> 3729 Desktop Computer Support Technician/14Feb05
> 3728 Desktop Computer Support Technician/12Feb05
> What I want to do is take Job ID 3731 and move it to the top like so:
> JobID jobTitle
> _______ ________________________________________
__
> 3731 Desktop Computer Support Technician/17Feb05
> 3734 Desktop Computer Support Technician/22Feb05
> 3733 Desktop Computer Support Technician/21Feb05
> 3732 Desktop Computer Support Technician/19Feb05
> 3730 Desktop Computer Support Technician/15Feb05
> 3729 Desktop Computer Support Technician/14Feb05
> 3728 Desktop Computer Support Technician/12Feb05
>
> I tried using a union ( I am sure there is a better way):
> Select JobID,
> jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
> ',''))
> from ApplicantResume a
> join Position p on (a.PositionID = p.PositionID)
> where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
> and
> Email = 'tscheid@.yahoo.com' and
> JobID = 3731
> union
> Select JobID,
> jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
> ',''))
> from ApplicantResume a
> join Position p on (a.PositionID = p.PositionID)
> where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
> and
> Email = 'tscheid@.yahoo.com' and
> JobID <> 3731
> order by datesubmitted desc
> I got an error:
> Server: Msg 104, Level 15, State 1, Line 1
> ORDER BY items must appear in the select list if the statement contains a
> UNION operator.
> I put datesubmitted in the select statment and got the following:
> JobID jobTitle
> _______ ________________________________________
__
> 3734 Desktop Computer Support Technician/22Feb05
> 3733 Desktop Computer Support Technician/21Feb05
> 3732 Desktop Computer Support Technician/19Feb05
> 3731 Desktop Computer Support Technician/17Feb05
> 3730 Desktop Computer Support Technician/15Feb05
> 3729 Desktop Computer Support Technician/14Feb05
> 3728 Desktop Computer Support Technician/12Feb05
> I assume it did the union and then order by, which negated what I was
> trying to do.
> How do I do the order by, just on the 2nd select and have it put the 2
> together?
Select 1 as temp,JobID,
jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
',''))
from ApplicantResume a
join Position p on (a.PositionID = p.PositionID)
where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
and
Email = 'tscheid@.yahoo.com' and
JobID = 3731
union
Select 2 as temp,JobID,
jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
',''))
from ApplicantResume a
join Position p on (a.PositionID = p.PositionID)
where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
and
Email = 'tscheid@.yahoo.com' and
JobID <> 3731
order by temp,datesubmitted desc
This seems to work, but was wondering if there was a better way than using 2
selects?
Thanks,
Tom|||>> This seems to work, but was wondering if there was a better way than
Use a CASE expression in your SELECT list & use its alias in the ORDER BY or
use a CASE expression directly in the ORDER BY clause. In your case, you can
have one like:
ORDER BY
CASE WHEN JobID = 3731 THEN 1 ELSE 2 END, datesubmitted DESC ;
Anith|||Try:
Select JobID,
jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
',''))
from ApplicantResume a
join Position p on (a.PositionID = p.PositionID)
where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
and
Email = 'tscheid@.yahoo.com'
order by case when JobID=3731 then 0 else 1 end asc, datesubmitted desc
-oj
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OEK6BxSGFHA.2296@.TK2MSFTNGP15.phx.gbl...
>I have a routine that is displays my record in date order.
> I want to be able to put a specific record from that set of rows on top,
> with the rest in date order (as it is now, but without the one I moved to
> the top.
> For example, I have the following select:
> Select JobID,
> jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
> ',''))
> from ApplicantResume a
> join Position p on (a.PositionID = p.PositionID)
> where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
> and
> Email = 'tscheid@.yahoo.com'
> order by datesubmitted desc
> This gives me the following:
> JobID jobTitle
> _______ ________________________________________
__
> 3734 Desktop Computer Support Technician/22Feb05
> 3733 Desktop Computer Support Technician/21Feb05
> 3732 Desktop Computer Support Technician/19Feb05
> 3731 Desktop Computer Support Technician/17Feb05
> 3730 Desktop Computer Support Technician/15Feb05
> 3729 Desktop Computer Support Technician/14Feb05
> 3728 Desktop Computer Support Technician/12Feb05
> What I want to do is take Job ID 3731 and move it to the top like so:
> JobID jobTitle
> _______ ________________________________________
__
> 3731 Desktop Computer Support Technician/17Feb05
> 3734 Desktop Computer Support Technician/22Feb05
> 3733 Desktop Computer Support Technician/21Feb05
> 3732 Desktop Computer Support Technician/19Feb05
> 3730 Desktop Computer Support Technician/15Feb05
> 3729 Desktop Computer Support Technician/14Feb05
> 3728 Desktop Computer Support Technician/12Feb05
>
> I tried using a union ( I am sure there is a better way):
> Select JobID,
> jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
> ',''))
> from ApplicantResume a
> join Position p on (a.PositionID = p.PositionID)
> where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
> and
> Email = 'tscheid@.yahoo.com' and
> JobID = 3731
> union
> Select JobID,
> jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
> ',''))
> from ApplicantResume a
> join Position p on (a.PositionID = p.PositionID)
> where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
> and
> Email = 'tscheid@.yahoo.com' and
> JobID <> 3731
> order by datesubmitted desc
> I got an error:
> Server: Msg 104, Level 15, State 1, Line 1
> ORDER BY items must appear in the select list if the statement contains a
> UNION operator.
> I put datesubmitted in the select statment and got the following:
> JobID jobTitle
> _______ ________________________________________
__
> 3734 Desktop Computer Support Technician/22Feb05
> 3733 Desktop Computer Support Technician/21Feb05
> 3732 Desktop Computer Support Technician/19Feb05
> 3731 Desktop Computer Support Technician/17Feb05
> 3730 Desktop Computer Support Technician/15Feb05
> 3729 Desktop Computer Support Technician/14Feb05
> 3728 Desktop Computer Support Technician/12Feb05
> I assume it did the union and then order by, which negated what I was
> trying to do.
> How do I do the order by, just on the 2nd select and have it put the 2
> together?
> Thanks,
> Tom
>|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ufKXI4SGFHA.208@.TK2MSFTNGP12.phx.gbl...
> Use a CASE expression in your SELECT list & use its alias in the ORDER BY
> or use a CASE expression directly in the ORDER BY clause. In your case,
> you can have one like:
> ORDER BY
> CASE WHEN JobID = 3731 THEN 1 ELSE 2 END, datesubmitted DESC ;
That did it.
Thanks,
Tom|||"oj" <nospam_ojngo@.home.com> wrote in message
news:%232JQi4SGFHA.1932@.TK2MSFTNGP14.phx.gbl...
> Try:
> Select JobID,
> jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
> ',''))
> from ApplicantResume a
> join Position p on (a.PositionID = p.PositionID)
> where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
> and
> Email = 'tscheid@.yahoo.com'
> order by case when JobID=3731 then 0 else 1 end asc, datesubmitted desc
That did it.
Thanks,
Tom