SQL Server Sequence number issue

Question

Hello, Last week Damir Sudarevic in this community helped with this query to generate a seq number. I have one issue with couple of issues with this one. For some reason records are not displayed by OrderDetailsID in spite of specifying it in order by.

The two columns in the query below seq and seqNo displays records as shown below

Seq  SeqNO
1A  1
2A  2
2B  2A
2C  2B
3A  3
3B  3A
3C  3B

Instead how do I get it as shown below

SeqNo
1
2A
2B
2C
3A
3B
3C

WITH  OrderDetails 
        AS ( SELECT prodcode 
                   ,prodDesc 
                   ,orderID
                   ,OrderDetailID
                   ,DENSE_RANK() OVER ( ORDER BY prodCode) AS [RnkSeq] 
                   ,ROW_NUMBER() OVER ( PARTITION BY prodCode ORDER BY OrderDetailID ) AS [NumSeq] 
             FROM   OrderDetails where orderID=65303 
           ) 
  SELECT  OrderDetailID 
         ,prodcode 
         ,CAST(RnkSeq AS varchar(10)) + CHAR(64 + NumSeq) as Seq
         ,Replace(CAST(RnkSeq AS varchar(10)) + CHAR(63 + NumSeq),'@','') AS SeqNo
         ,orderID  
  FROM    OrderDetails  

Answer 1

I don't see an ORDER BY ?

Answer 2

Add this at the end, after the FROM statement:

ORDER BY SeqNo

Hope this helps.