Azure SQL Database
An Azure relational database service.
5,444 questions
Check a conceptual sample code:
. . .
select *
from CTE
order by parent_id, sequence_number
Unable to sort during creation of a recursive CTE IN SQL Server
salilsingh-9961
346
Reputation points
Hi Team, In SQL Server stored procedure. I am working on creating a recursive CTE which will show the output of hierarchical data. One parent is having multiple child's, i need to sort all child's of a parent, a sequence number field is given for same. Can you please provide a sample code for same. Thanks, Salil
{count} votes
-
PercyTang-MSFT 12,426 Reputation points Microsoft Vendor2023-04-10T02:48:32.24+00:00 Hi @salilsingh-9961
Can you show the source table and the expected result, which can help solve the problem? -
PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
2023-04-14T08:52:25.2466667+00:00 Hi @salilsingh-9961
We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
Sign in to comment
1 answer
Sort by: Most helpful
-
Viorel 114.7K Reputation points2023-04-09T18:00:43.0133333+00:00 -
salilsingh-9961 346 Reputation points
2023-04-09T18:09:27.17+00:00 Hi Viorel, I am doing the same but once above data is inserted in a main table, I still see the same sequence which is not sorted, even my query shows the sorted data but after insertion the data is not sorted. I am doing Insert into a table when selecting /sorting the records of CTE, what wrong I might be doing here, surely while inserting a data from select statement which is sorting the data, data must be getting inserted after sort has happened on select statement and not before it. Thanks, Salil
-
Erland Sommarskog 107.2K Reputation points2023-04-09T18:45:33.8166667+00:00 I still see the same sequence which is not sorted, even my query shows the sorted data but after insertion the data is not sorted.
A table is by definition an unsorted set. You need to use ORDER BY when you run a query to get a certain order.
-
Viorel 114.7K Reputation points
2023-04-09T18:54:04.66+00:00 You must use ORDER BY when you check the main table, otherwise the order is unspecified.
Sign in to comment -