rowidentity reuse in cte

Question: what is the correct syntax when selecting
rowid recieving the row number using scope identity or other and I want to pass the value inside my common table expression select statement

other words I will I select the row value and pass it to my table.


I do not have a row number in my select so I will like to pass the row number to my table select within my cte select statement.
LVL 9
Seven priceFull StackAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Snarf0001Commented:
Not sure if I got that completely, but it sounds like you want in id-type column in a cte with row number?

; with cte as
(
  select col1, col2, Row_Number() over (order by col3) as RowNumber
  from table1
)
select * from cte
0
Seven priceFull StackAuthor Commented:
>>; with cte as
(
  select col1, col2, Row_Number() over (order by col3) as RowNumber
  from table1
)
select * from cte

ok but is it possible to have the row number desc and the rest of my columns asc ??
0
Snarf0001Commented:
Sorry my friend, still not quite sure what you mean, but short answer is yes, it's possible.

Either:

select col1, col2, Row_Numer() over (order by col2 desc, col1, desc, col3 asc)
--will do a different ordering for the actual rownumber


OR:

select * from cte
Order by col1 asc, Rownumber desc etc...
--you can still do a completely differnet ordering in the final select, apart from what was used to create the rownumber
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
Or:

(
  select col1, col2, Row_Number() over (order by col3 DESC) as RowNumber
  from table1
)
select * from cte
ORDER BY Col3 ASC

Open in new window

0
Seven priceFull StackAuthor Commented:
tjk
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.