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

Order by Issue

I am having a small issue with the order by command.

Everytime I run the code below, everything displays in the correct order which was assigned by the order by command:

select *
from [table1]
order by column_a, column_b, column_c

My problem is that I want it to be stored in another table in the order that I specify, so I run the code below which has the same order:

select *
into [new table]
from [table1]
order by column_a, column_b, column_c

My problem is that when I do a select * form the new table that I inserted all the information into in order, it will not be returned in order. Why would that be? My understanding is that it would store the info in the new table by the way I specified on the order by line.

Any help would be appreciated. It is driving me crazy!Data in a recordset has no order unless specified. While you may have specified an order when you inserted the records, there is no guarantee that they will be maintained in that order, or returned in that order.

You can ensure that they are stored in a specific order by placing a clustered index on the columns you want, and MOST of the time queries against the table will return the records in that order, but again there is no guarantee.

You should always issue an ORDER BY clause on all SQL Statements which require data to be returned in a specified order.|||The only time the resultset will be in the order of the clustered index is if you run your SELECT on a single processor machine, or if you set only 1 processor for SQL Server service, or if you use OPTION (MAXDOP 1). In any other case the order of the clustered index does not affect the order of the resultset without ORDER BY.