Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

order by clause based on condition

Posted on 2014-03-26
11
Medium Priority
?
312 Views
Last Modified: 2014-03-27
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

0
Comment
Question by:chaitu chaitu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +3
11 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39958130
select *
from  employee
order by priority desc
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 39958150
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
0
 
LVL 5

Expert Comment

by:jayakrishnabh
ID: 39958214
;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
0
Amazon Web Services EC2 Cheat Sheet

AWS EC2 is a core part of AWS’s cloud platform, allowing users to spin up virtual machines for a variety of tasks; however, EC2’s offerings can be overwhelming. Learn the basics with our new AWS cheat sheet – this time on EC2!

 
LVL 20

Author Comment

by:chaitu chaitu
ID: 39958222
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
0
 
LVL 5

Expert Comment

by:jayakrishnabh
ID: 39958258
;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
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 39958294
not working..
0
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 200 total points
ID: 39958335
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
0
 
LVL 35

Assisted Solution

by:ste5an
ste5an earned 600 total points
ID: 39958409
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

0
 
LVL 9

Expert Comment

by:sarabhai
ID: 39958451
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
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1200 total points
ID: 39958585
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.
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 39958704
thank you for the  wonderful answers.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question