Common Table Expression [Resolved]

Posted by Murugavelmsc under Sql Server on 3/14/2013 | Points: 10 | Views : 5969 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi,

;with emp_cte(row_number1)
as
(
select *, ROW_NUMBER() over ( partition by id order by firstname, lname, salary) as row_number1 from emp
) delete from emp_cte where row_number1>1

it throws an following error

'emp_cte' has more columns than were specified in the column list.

Please help me out

Regards,
Murugavel S
murugavel.sadagopan@gmail.com
http://murugavelmsc.blogspot.in/

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Pandians on: 3/14/2013 [Member] [MVP] Silver | Points: 50

0	Try this! ;with emp_cte as ( select , ROW_NUMBER() over ( partition by id order by firstname, lname, salary) as row_number1 from emp ) delete from emp_cte where row_number1>1 Because, you are trying to result morethan 1 column. But, you have forced to single one "row_number1" Note: all the value from CTE should have column name! Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Murugavelmsc, if this helps please login to Mark As Answer*. \| Alert Moderator

Try this!

;with emp_cte

as

(

select *, ROW_NUMBER() over ( partition by id order by firstname, lname, salary) as row_number1 from emp

) 



delete from emp_cte where row_number1>1

Because, you are trying to result morethan 1 column. But, you have forced to single one "row_number1"

Note: all the value from CTE should have column name!

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Murugavelmsc on: 3/14/2013 [Member] Starter | Points: 25

0	Hi Pandian, Thanks a lot... it works fine Regards, Murugavel S murugavel.sadagopan@gmail.com http://murugavelmsc.blogspot.in/ Murugavelmsc, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts