Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Row_number with Case Statement

Author  Topic 

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2013-07-28 : 18:08:56
Hi, I am using Row_Number function to identify orders placed by a customer. What i want to do is create a new column and state what order it is. For example when Row_Number = '1' then then it should state 'First Order' in the new column, when Row_Number = '2' then second order and so on.

select
o.*
from
(select
a.orderid,
a.orderdate,
ROW_NUMBER () Over (Partition by a.orderid order by a.orderdate desc) as RN
from
dbo.Orders a
where
a.ShippedDate is not null
) as o

I want to include a case statement like this in the above query to call upon 'RN' - Rownumber

Case when RN = 1 THEN 'First Order'
When RN = 2 THEN 'Second Order'
Else ''
End

Thanks in advance for the help

M. Ncube

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-28 : 18:19:47
[code]select o.*,
case
when rn = 1 then 'First order'
when rn = 2 then 'Second order'
else ''
end
from (
select orderid,
orderdate,
ROW_NUMBER () Over (Partition by customerid order by orderdate) as RN
from dbo.Orders
where ShippedDate is not null
) as o[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2013-07-28 : 18:26:14
Thank you very much !!! Its working.

M. Ncube
Go to Top of Page

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2013-07-28 : 18:36:30
If you have another alternative to this, your help would be appreciated. Thanks

M. Ncube
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-28 : 19:26:38
Here is another way:
[CODE]
select o.*,
CAST(rn as VARCHAR) + ' Order' as Comments
from (
select orderid,
orderdate,
ROW_NUMBER () Over (Partition by customerid order by orderdate) as RN
from dbo.Orders
where ShippedDate is not null
) as o


[/CODE]
If you have SQL 2012, you can use CHOOSE function as below:

[CODE]

select o.*,
COALESCE(CHOOSE(rn, 'First order', 'Second order', 'Third order', 'Fourth order', 'Fifth Order'), '') as Comments
from (
select orderid,
orderdate,
ROW_NUMBER () Over (Partition by customerid order by orderdate) as RN
from dbo.Orders
where ShippedDate is not null
) as o

[/CODE]
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-29 : 02:12:56
quote:
Originally posted by marcusn25

If you have another alternative to this, your help would be appreciated. Thanks
What do you mean with alternative?


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
   

- Advertisement -