August 25, 2010 at 5:19 am
Hi All
In the below query, I want to sort on the Column BigOrSmall(which I calculate based on a case statement). I am not sure if this can be done inside Order by of a row()number funtion. I have commented the code in query which Gives error. I am putting table and sample data for your ease here.
Also I dont want to put this column in Order By clause of last select statement as I have implemented paging as well, which I have not put here for simplicity.
CREATE TABLE CAPITALS(CapId int, CapName varchar(20), area int)
insert into CAPITALS values (1,'DC',5556)
insert into CAPITALS values (3,'Delhi',7894)
insert into CAPITALS values (2,'Perth',2381)
insert into CAPITALS values (4,'Colombo',9998)
insert into CAPITALS values (7,'London',2335667)
insert into CAPITALS values (5,'Thimpu',5556)
insert into CAPITALS values (6,'Kathmandu',1111)
insert into CAPITALS values (9,'Beijing',77756)
insert into CAPITALS values (8,'Johansburg',65)
declare @sortOrder int
SET @sortOrder=2
;With MyCte as
(SELECT Row_number() OVER( Order by CASE WHEN @sortOrder=1 THEN CapId
--WHEN 2 THEN (CASE WHEN Area <2000 THEN 'Small'
-- WHEN Area >=2000 and Area <5000 THEN 'Medium'
-- WHEN Area >5000 THEN 'Big'
-- END 'BigOrSmall')
END) AS 'RowNumber',
CASE WHEN Area <2000 THEN 'Small'
WHEN Area >=2000 and Area <5000 THEN 'Medium'
WHEN Area >5000 THEN 'Big'
END as 'BigOrSmall',
CapName
FROM CAPITALS)
Select * from MyCte
Order by Rownumber
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
August 25, 2010 at 6:10 am
Hi All
I finally was able to do it this way:
declare @sortOrder int
SET @sortOrder=2
;With MyCte as
(SELECT CASE WHEN @sortOrder=1 THEN Row_Number() Over(Order By Capid)
WHEN @sortOrder=2 THEN Row_Number() Over(Order By (CASE WHEN Area <2000 THEN 'Small'
WHEN Area >=2000 and Area <5000 THEN 'Medium'
WHEN Area >5000 THEN 'Big' END)) END 'Rownumber',
CASE WHEN Area <2000 THEN 'Small'
WHEN Area >=2000 and Area <5000 THEN 'Medium'
WHEN Area >5000 THEN 'Big'
END as 'BigOrSmall',
CapName
FROM CAPITALS)
Select * from MyCte
Order by Rownumber
Hope it helps somebody......
Alternative ways or enhancements are welcome....
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
August 25, 2010 at 6:38 am
One alternative could be this
;WITH MyCte AS
(
SELECTROW_NUMBER() OVER( ORDER BY CASE
WHEN @sortOrder = 1 THEN CapId
WHEN @sortOrder = 2 AND Area < 2000 THEN 3 -- Say 3 for Small
WHEN @sortOrder = 2 AND Area >= 2000 AND Area < 5000 THEN 2 -- 2 for Medium
WHEN @sortOrder = 2 AND Area > 5000 THEN 1 -- 1 for Big
ELSE 0
END ) AS 'RowNumber',
CASE
WHEN Area < 2000 THEN 'Small'
WHEN Area >= 2000 AND Area < 5000 THEN 'Medium'
WHEN Area > 5000 THEN 'Big'
END AS 'BigOrSmall', CapName
FROMCAPITALS
)
SELECT*
FROMMyCte
ORDER BY Rownumber
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply