Solved

order by clause based on condition

Posted on 2014-03-26
11
307 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help With Database JOIN 7 28
How do I subtract date and time within a same column in SQL 4 38
This query failed in sql 2014 5 31
SQL Improvement  ( Speed) 14 28
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

778 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