SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Row_number with Case Statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

marcusn25
Yak Posting Veteran

Germany
50 Posts

Posted - 07/28/2013 :  18:08:56  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 07/28/2013 :  18:19:47  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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



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

marcusn25
Yak Posting Veteran

Germany
50 Posts

Posted - 07/28/2013 :  18:26:14  Show Profile  Reply with Quote
Thank you very much !!! Its working.

M. Ncube
Go to Top of Page

marcusn25
Yak Posting Veteran

Germany
50 Posts

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

M. Ncube
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/28/2013 :  19:26:38  Show Profile  Reply with Quote
Here is another way:

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



If you have SQL 2012, you can use CHOOSE function as below:



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

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 07/29/2013 :  02:12:56  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000