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.
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.selecto.*from(selecta.orderid,a.orderdate,ROW_NUMBER () Over (Partition by a.orderid order by a.orderdate desc) as RNfromdbo.Orders awherea.ShippedDate is not null) as oI want to include a case statement like this in the above query to call upon 'RN' - RownumberCase when RN = 1 THEN 'First Order' When RN = 2 THEN 'Second Order'Else ''EndThanks in advance for the helpM. 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 '' endfrom ( 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 |
|
|
marcusn25
Yak Posting Veteran
56 Posts |
Posted - 2013-07-28 : 18:26:14
|
Thank you very much !!! Its working.M. Ncube |
|
|
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. ThanksM. Ncube |
|
|
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 Commentsfrom ( 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 Commentsfrom ( 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] |
|
|
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 |
|
|
|
|
|
|
|