order by clause based on condition

create table  employee
(
id int,
location_id int,
priority int,
status varchar2(10)
)

sample_data
***********
insert into employee values(1,11111,10,'V')
insert into employee values(2,22222,11,'V')
insert into employee values(3,33333,1000,'V')
insert into employee values(4,44444,100,'V')

Open in new window


I need to find out employees based on priority excluding location_id '33333'.but that record also should come out from the final query.
for the above sample data the output should like this.

select *,row_number() over(order by priority desc)
from  employee

but from above query 33333 location_id is coming out at the top.

output
********
id   location_id priority status
4       44444 100
2       22222 11  
1       11111  10
3       33333  10000

Open in new window

LVL 20
chaitu chaituAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

PortletPaulEE Topic AdvisorCommented:
select *
from  employee
order by priority desc
chaitu chaituAuthor Commented:
then the out put will come like this.please see the question once again..

output
********
id   location_id priority
3       33333  10000
4       44444 100
2       22222 11  
1       11111  10
jayakrishnabhCommented:
;with cte1 as
(
select id, location_id, priority, [status], ROW_NUMBER() over (order by priority desc) as SlNo
from employee
where location_id<>33333
)
select * from cte1
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

chaitu chaituAuthor Commented:
it should not consider 33333 location_id as priority.the final output should be

output
********
id   location_id priority status
4       44444 100
2       22222 11  
1       11111  10
3       33333  10000
jayakrishnabhCommented:
;with cte1 as
(
select id, location_id, priority, ROW_NUMBER() over (order by priority desc) as SlNo
from employee
where location_id<>33333
)
, cte2 as (
select MAX(SlNo) as LastNum from cte1
)
,cte3 as (
select id, location_id, priority, SlNo from cte1
union
(select  e.id, e.location_id, e.priority, cte2.LastNum+1 from employee e,cte2 where e.location_id=33333)
)
select id, location_id, priority from cte3
order by SlNo asc
chaitu chaituAuthor Commented:
not working..
Pratima PharandeCommented:
try this

create table  #TempTable
(
id int,
location_id int,
priority int,
status varchar(10)
)



INSERT INTO #TempTable (ID, location_id, priority,status)
select * from employee
where Location_Id <> 33333
order by priority desc
INSERT INTO #TempTable (ID, location_id, priority,status)
select * from employee
where Location_Id = 33333

select * from #TempTable


drop table #TempTable
ste5anSenior DeveloperCommented:
Use the CASE expression, e.g.

DECLARE @employee TABLE
    (
      id INT ,
      location_id INT ,
      [priority] INT ,
      [status] VARCHAR(10)
    );

INSERT  INTO @employee
VALUES  ( 1, 11111, 10, 'V' ),
        ( 2, 22222, 11, 'V' ),
        ( 3, 33333, 1000, 'V' ),
        ( 4, 44444, 100, 'V' );

SELECT  *
FROM    @employee
ORDER BY CASE WHEN [priority] = 1000 THEN 1
              ELSE 0
         END ASC ,
        [priority] DESC;

Open in new window

sarabhaiCommented:
can you try this one

SELECT A.id,a.location_id,a.priority,a.status FROM
(
SELECT *,1 as o FROM  employee where priority != 1000  
union all
SELECT *,2 as o FROM  employee where priority = 1000  
) AS A order by o ,priority desc
PortletPaulEE Topic AdvisorCommented:
assuming zero is the lowest then try:
SELECT
      id
    , location_id
    , priority
    , [status]
FROM employee
ORDER BY
      (CASE WHEN location = 33333 THEN 0 ELSE priority END) DESC
;

Open in new window

if zero is already used then the case expression could use -1000 or something like that instead.

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
chaitu chaituAuthor Commented:
thank you for the  wonderful answers.
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
Microsoft SQL Server 2008

From novice to tech pro — start learning today.