Solved

order by clause based on condition

Posted on 2014-03-26
11
305 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
  • 4
  • 2
  • 2
  • +3
11 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
select *
from  employee
order by priority desc
0
 
LVL 20

Author Comment

by:chaitu chaitu
Comment Utility
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
Comment Utility
;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
 
LVL 20

Author Comment

by:chaitu chaitu
Comment Utility
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
Comment Utility
;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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 20

Author Comment

by:chaitu chaitu
Comment Utility
not working..
0
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 50 total points
Comment Utility
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 32

Assisted Solution

by:Stefan Hoffmann
Stefan Hoffmann earned 150 total points
Comment Utility
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
Comment Utility
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 48

Accepted Solution

by:
PortletPaul earned 300 total points
Comment Utility
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
Comment Utility
thank you for the  wonderful answers.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

In this article I will describe the Detach & Attach 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.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now