• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 461
  • Last Modified:

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.
0
Seven price
Asked:
Seven price
  • 2
  • 2
2 Solutions
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now