Common Table Expression [Resolved]

Posted by Murugavelmsc under Sql Server on 3/14/2013 | Points: 10 | Views : 2881 | Status : [Member] | Replies : 2
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/



Responses

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

Up
0
Down

Resolved
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

Up
0
Down
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

Login to post response