none
Need tsql help

    Question

  • Hi Experts,

    Need some help in tsql. This is my table. I have put some sample data and expecting output as shown in the below screenshot .

    For every new start 1,2 a new txnid should be generated which means those 2 rows belong to one transaction.

    Please help.

    CREATE TABLE [dbo].[test](
    [id] [int] NULL,
    [c1] [varchar](100)
    )
    GO
    INSERT [dbo].[test] ([id], [c1]) VALUES (1, N'zsdssfd')
    GO
    INSERT [dbo].[test] ([id], [c1]) VALUES (2, N'yosmd')
    GO
    INSERT [dbo].[test] ([id], [c1]) VALUES (1, N'flodC')
    GO
    INSERT [dbo].[test] ([id], [c1]) VALUES (2, N'ioppsps')
    GO
    INSERT [dbo].[test] ([id], [c1]) VALUES (1, N'dvev')
    GO
    INSERT [dbo].[test] ([id], [c1]) VALUES (2, N'vedmdm')
    GO


    select * from test;

    Expected output:


    Thanks,

    Sam





    • Edited by Samantha v Saturday, November 11, 2017 6:18 AM
    Saturday, November 11, 2017 6:16 AM

Answers

  • Then its very easy

    all you need is this

    CREATE TABLE [dbo].[test](
     sno int identity(1,1),
    [id] [int] NULL,
    [c1] [varchar](100)
    )
    GO
    INSERT [dbo].[test] ([id], [c1]) VALUES (1, N'zsdssfd')
    GO
    INSERT [dbo].[test] ([id], [c1]) VALUES (2, N'yosmd')
    GO
    INSERT [dbo].[test] ([id], [c1]) VALUES (1, N'flodC')
    GO
    INSERT [dbo].[test] ([id], [c1]) VALUES (2, N'ioppsps')
    GO
    INSERT [dbo].[test] ([id], [c1]) VALUES (1, N'dvev')
    GO
    INSERT [dbo].[test] ([id], [c1]) VALUES (2, N'vedmdm')
    GO
    
    select *,
    row_number() over (partition by id order by sno) as txnid
     from test
     order by sno
    
    
    
    /*
    
    output
    -----------------------------------
    sno	id	c1	txnid
    -----------------------------------
    1	1	zsdssfd	1
    2	2	yosmd	1
    3	1	flodC	2
    4	2	ioppsps	2
    5	1	dvev	3
    6	2	vedmdm	3
    
    
    */


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Ashish Pandey Saturday, November 11, 2017 9:55 AM
    • Marked as answer by Samantha v Saturday, November 11, 2017 10:53 AM
    Saturday, November 11, 2017 8:24 AM

All replies

  • Do you've any other key (unique valued) columns in the table?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Saturday, November 11, 2017 6:30 AM
  • You shall use something like these in any case

    SELECT DENSE_RANK() OVER (ORDER BY Bseq) AS Seq,
    id,c1
    FROM
    (
    
    SELECT (ROW_NUMBER() OVER (ORDER BY (select 1)) - id ) AS BSeq,
    * 
    FROM test
    )t
    ORDER BY Seq,id


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Saturday, November 11, 2017 6:35 AM
  • Hi Visakh,

    Thanks a lot.

    As you have mentioned suppose , I have introduced an new key(unique value column say an identity column), How does my dense_rank query change??

    drop table test;


    CREATE TABLE [dbo].[test](
     sno int identity(1,1),
    [id] [int] NULL,
    [c1] [varchar](100)
    )
    GO
    INSERT [dbo].[test] ([id], [c1]) VALUES (1, N'zsdssfd')
    GO
    INSERT [dbo].[test] ([id], [c1]) VALUES (2, N'yosmd')
    GO
    INSERT [dbo].[test] ([id], [c1]) VALUES (1, N'flodC')
    GO
    INSERT [dbo].[test] ([id], [c1]) VALUES (2, N'ioppsps')
    GO
    INSERT [dbo].[test] ([id], [c1]) VALUES (1, N'dvev')
    GO
    INSERT [dbo].[test] ([id], [c1]) VALUES (2, N'vedmdm')
    GO

    select * from test;

    Saturday, November 11, 2017 7:27 AM
  • Then its very easy

    all you need is this

    CREATE TABLE [dbo].[test](
     sno int identity(1,1),
    [id] [int] NULL,
    [c1] [varchar](100)
    )
    GO
    INSERT [dbo].[test] ([id], [c1]) VALUES (1, N'zsdssfd')
    GO
    INSERT [dbo].[test] ([id], [c1]) VALUES (2, N'yosmd')
    GO
    INSERT [dbo].[test] ([id], [c1]) VALUES (1, N'flodC')
    GO
    INSERT [dbo].[test] ([id], [c1]) VALUES (2, N'ioppsps')
    GO
    INSERT [dbo].[test] ([id], [c1]) VALUES (1, N'dvev')
    GO
    INSERT [dbo].[test] ([id], [c1]) VALUES (2, N'vedmdm')
    GO
    
    select *,
    row_number() over (partition by id order by sno) as txnid
     from test
     order by sno
    
    
    
    /*
    
    output
    -----------------------------------
    sno	id	c1	txnid
    -----------------------------------
    1	1	zsdssfd	1
    2	2	yosmd	1
    3	1	flodC	2
    4	2	ioppsps	2
    5	1	dvev	3
    6	2	vedmdm	3
    
    
    */


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Ashish Pandey Saturday, November 11, 2017 9:55 AM
    • Marked as answer by Samantha v Saturday, November 11, 2017 10:53 AM
    Saturday, November 11, 2017 8:24 AM
  • Thank you very much for the help. The output is coming as expected. Thanks.

    Sam

    Saturday, November 11, 2017 10:53 AM