Home 2017 October SQL Puzzle: SQL Advance Query – Generate Row number without any default function

SQL Puzzle: SQL Advance Query – Generate Row number without any default function

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Check the below input data and expected output to generate the Row number without default function.

Input Data:

Expected Output:

Create a table with data:

Solution:

Please try the different solution for this puzzle and share it via comment...

Anvesh Patel
SQL Puzzle: SQL Advance Query - Find the Square of first Ten NumberSQL Puzzle: SQL Advance Query - Find the range of missing years
Comments: 6
  1. Dinesh.IS
    October 11, 2017 at 1:28 pm

    –Method-1
    Declare @GenerateUniqueNumber Table(ID Int,RowNumber Int)
    Declare @Amt Int;
    Declare @Cnt As Int;
    Declare @ID As Int;
    Select @Amt=Min(ID) From TBL_Numbers
    Set @ID=0
    While @Amt Is Not Null
    Begin

    Insert Into @GenerateUniqueNumber
    (ID,RowNumber)
    Values(@ID,@Amt)
    Set @ID=@ID+1
    Select @Amt=Min(ID) From TBL_Numbers Where ID>@Amt
    End

    ReplyCancel
  2. Dinesh.IS
    October 11, 2017 at 1:29 pm

    –Method-2
    Declare @GenerateUniqueNumber Table(ID Int,RowNumber Int)
    Declare @ID As Int
    Declare @Row As Int;
    Set @Row=0
    Declare Cur Cursor For Select ID From TBL_Numbers
    Open Cur
    Fetch Next From Cur Into @ID
    While @@Fetch_Status=0
    Begin

    Insert Into @GenerateUniqueNumber
    Values(@ID,@Row)
    Set @Row=@Row+1

    Fetch Next From Cur Into @ID
    End
    Close Cur
    Deallocate Cur
    Select * From @GenerateUniqueNumber

    ReplyCancel
  3. Dinesh.IS
    October 11, 2017 at 1:31 pm

    –Method-3
    ;
    WITH CTE
    As
    (Select * From TBL_Numbers)
    Select *,(Select Count(ID) From CTE B Where B.ID<A.ID) As RowNumber
    From CTE A
    Order By RowNumber

    ReplyCancel
  4. Dinesh.IS
    October 12, 2017 at 7:04 am

    –This is best way to reach the goal
    Declare @RowNumber Table(ID Int,RowNumber Int Identity(1,1) Not Null)
    Insert Into @RowNumber
    Select * From TBL_Numbers

    Select * From @RowNumber

    ReplyCancel
  5. Phanindra Suripeddi
    August 2, 2019 at 8:03 am

    select id,(select count(1) From tbl_Numbers b where b.id<=a.id) Rownumber From tbl_Numbers a
    Order by Rownumber

    ReplyCancel

Leave a Reply to Dinesh.IS Cancel reply

CAPTCHA

*

Anvesh Patel
Anvesh Patel

Database Engineer

October 4, 2017 6 Comments SQL Puzzle, , , , , , , , , , ,