Solved

order by clause based on condition

Posted on 2014-03-26
11
306 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
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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 20

Author Comment

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

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 50 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 33

Assisted Solution

by:ste5an
ste5an earned 150 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 48

Accepted Solution

by:
PortletPaul earned 300 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

910 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

23 Experts available now in Live!

Get 1:1 Help Now