none
Can I do this with ROW_NUMBER()?

    Question

  • Hi All,

    I've used Row_Number before to created numbered recordsets, but I'm having trouble getting it right this time. I'll give you my table definition then show you what I'd like to get.

    CREATE TABLE [dbo].[My_Table](
    	[SEQ_NBR] [int] NOT NULL,
    	[FROM_DT] [date] NULL,
    	[CLM_ID] [varchar](20) NULL,
    	[DTL_REC_NBR] [int] NULL,
    	[ADJ_REC_NBR] [int] NULL,
            [PAID_DENIED_IND] [char](1) NULL
    ) 
    The CLM_ID field is not unique, nor is the FROM_DT, DTL_REC_NBR, ADJ_REC_NBR. The SEQ_NBR is a supplied row identifier.

    Here is the logic the best way I can explain it, since I can't seem to get it right with code. I need the ROW_NUMBER() to be the same if the CLM_ID, FROM_DT, and PAID_DENIED_IND are the same

    Here is what I have so far, but this creates a new ROW_NUMBER() for each record with the same CLM_ID, FROM_DT and PAID_DENIED_IND.

    SELECT SEQ_NBR, FROM_DT, CLM_ID, DTL_REC_NBR, ADJ_REC_NBR, ROW_NUMBER() OVER (PARTITION BY CLM_ID, FROM_DT, PAID_DENIED_IND ORDER BY CLM_ID, FROM_DT, PAID_DENIED_IND DESC, DTL_REC_NBR, ADJ_REC_NBR) AS Row_Num
    FROM dbo.My_Table
    I know this probably wasn't explained very well, but I"m not sure how else to describe it. Please let me know if you have any questions! Thank you so much!

    Monday, August 05, 2013 8:17 PM

Answers

  • I believe you are looking for DENSE_RANK instead of ROW_NUMBER.

    Check the below link and ask if you have more specific question:

    http://blog.sqlauthority.com/2007/10/09/sql-server-2005-sample-example-of-ranking-functions-row_number-rank-dense_rank-ntile/


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, August 06, 2013 12:13 AM
  • I think what you need is more of a group id, something like this:

    USE tempdb
    GO
    
    SET NOCOUNT ON
    
    IF OBJECT_ID('dbo.My_Table') IS NOT NULL DROP TABLE dbo.My_Table
    GO
    CREATE TABLE dbo.My_Table(
    	SEQ_NBR INT NOT NULL,
    	FROM_DT DATE NULL,
    	CLM_ID VARCHAR(20) NULL,
    	DTL_REC_NBR INT NULL,
    	ADJ_REC_NBR INT NULL,
        PAID_DENIED_IND CHAR(1) NULL
    )
    GO
    
    INSERT INTO dbo.My_Table ( SEQ_NBR, FROM_DT, CLM_ID, DTL_REC_NBR, ADJ_REC_NBR, PAID_DENIED_IND )
    VALUES 
    	( 100, '1 Jan 2013', 1, 9, 99, 'N' ),		-- groupId should be 1
    
    	( 201, '2 Jan 2013', 2, 10, 101, 'N' ),		-- groupId should be 3
    	( 202, '2 Jan 2013', 2, 11, 102, 'N' ),		-- groupId should be 3
    	( 203, '2 Jan 2013', 2, 10, 101, 'Y' ),		-- groupId should be 2
    	( 204, '2 Jan 2013', 2, 11, 102, 'N' ),		-- groupId should be 3
    
    	( 401, '4 Jan 2013', 4, 11, 102, 'Y' ),		-- groupId should be 4
    	( 402, '4 Jan 2013', 4, 12, 103, 'Y' ),		-- groupId should be 4
    	( 403, '4 Jan 2013', 4, 13, 104, 'Y' ),		-- groupId should be 4
    	( 404, '4 Jan 2013', 4, 14, 105, 'Y' ),		-- groupId should be 4
    
    	( 404, '4 Jan 2013', 4, 14, 105, 'N' )		-- groupId should be 5
    GO 
    
    
    SELECT *, 
    	DENSE_RANK() OVER( ORDER BY CLM_ID, FROM_DT, PAID_DENIED_IND DESC ) groupId
    FROM dbo.My_Table
    ORDER BY groupId, SEQ_NBR

    If you have a PARTITION BY in a ranking function, it causes the numbering to be reset to 1 for the end of that group, whereas I think what you want is the group id to ascend.

    Sample data and expected results really help when posting problems like this.

    • Edited by wBob Tuesday, August 06, 2013 8:19 AM cte removed
    • Marked as answer by chipmunkofdoom2 Tuesday, August 06, 2013 1:55 PM
    Tuesday, August 06, 2013 8:06 AM
  • why dont you change you table schema, and add a identity field to it.

    CREATE TABLE [dbo].[My_Table](

    [SEQ_NBR] [int] NOT NULL,[SEQ1_nbr] [int] identity not null, [FROM_DT] [date] NULL, [CLM_ID] [varchar](20) NULL, [DTL_REC_NBR] [int] NULL, [ADJ_REC_NBR] [int] NULL, [PAID_DENIED_IND] [char](1) NULL )


    or change the seq_nbr to be a Identity

    CREATE TABLE [dbo].[My_Table](

    [SEQ_NBR] [int] IDENTITY NOT NULL, [FROM_DT] [date] NULL, [CLM_ID] [varchar](20) NULL, [DTL_REC_NBR] [int] NULL, [ADJ_REC_NBR] [int] NULL, [PAID_DENIED_IND] [char](1) NULL )


    Nothing is Permanent... even Knowledge.... My Blog

    Tuesday, August 06, 2013 8:21 AM

All replies

  • Well what you are doing looks alright. But, If you are looking for something specific please post some sample data and desired resultset to help you out better.

    Thanks...........


    Ione

    Monday, August 05, 2013 8:39 PM
  • I believe you are looking for DENSE_RANK instead of ROW_NUMBER.

    Check the below link and ask if you have more specific question:

    http://blog.sqlauthority.com/2007/10/09/sql-server-2005-sample-example-of-ranking-functions-row_number-rank-dense_rank-ntile/


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, August 06, 2013 12:13 AM
  • I think what you need is more of a group id, something like this:

    USE tempdb
    GO
    
    SET NOCOUNT ON
    
    IF OBJECT_ID('dbo.My_Table') IS NOT NULL DROP TABLE dbo.My_Table
    GO
    CREATE TABLE dbo.My_Table(
    	SEQ_NBR INT NOT NULL,
    	FROM_DT DATE NULL,
    	CLM_ID VARCHAR(20) NULL,
    	DTL_REC_NBR INT NULL,
    	ADJ_REC_NBR INT NULL,
        PAID_DENIED_IND CHAR(1) NULL
    )
    GO
    
    INSERT INTO dbo.My_Table ( SEQ_NBR, FROM_DT, CLM_ID, DTL_REC_NBR, ADJ_REC_NBR, PAID_DENIED_IND )
    VALUES 
    	( 100, '1 Jan 2013', 1, 9, 99, 'N' ),		-- groupId should be 1
    
    	( 201, '2 Jan 2013', 2, 10, 101, 'N' ),		-- groupId should be 3
    	( 202, '2 Jan 2013', 2, 11, 102, 'N' ),		-- groupId should be 3
    	( 203, '2 Jan 2013', 2, 10, 101, 'Y' ),		-- groupId should be 2
    	( 204, '2 Jan 2013', 2, 11, 102, 'N' ),		-- groupId should be 3
    
    	( 401, '4 Jan 2013', 4, 11, 102, 'Y' ),		-- groupId should be 4
    	( 402, '4 Jan 2013', 4, 12, 103, 'Y' ),		-- groupId should be 4
    	( 403, '4 Jan 2013', 4, 13, 104, 'Y' ),		-- groupId should be 4
    	( 404, '4 Jan 2013', 4, 14, 105, 'Y' ),		-- groupId should be 4
    
    	( 404, '4 Jan 2013', 4, 14, 105, 'N' )		-- groupId should be 5
    GO 
    
    
    SELECT *, 
    	DENSE_RANK() OVER( ORDER BY CLM_ID, FROM_DT, PAID_DENIED_IND DESC ) groupId
    FROM dbo.My_Table
    ORDER BY groupId, SEQ_NBR

    If you have a PARTITION BY in a ranking function, it causes the numbering to be reset to 1 for the end of that group, whereas I think what you want is the group id to ascend.

    Sample data and expected results really help when posting problems like this.

    • Edited by wBob Tuesday, August 06, 2013 8:19 AM cte removed
    • Marked as answer by chipmunkofdoom2 Tuesday, August 06, 2013 1:55 PM
    Tuesday, August 06, 2013 8:06 AM
  • why dont you change you table schema, and add a identity field to it.

    CREATE TABLE [dbo].[My_Table](

    [SEQ_NBR] [int] NOT NULL,[SEQ1_nbr] [int] identity not null, [FROM_DT] [date] NULL, [CLM_ID] [varchar](20) NULL, [DTL_REC_NBR] [int] NULL, [ADJ_REC_NBR] [int] NULL, [PAID_DENIED_IND] [char](1) NULL )


    or change the seq_nbr to be a Identity

    CREATE TABLE [dbo].[My_Table](

    [SEQ_NBR] [int] IDENTITY NOT NULL, [FROM_DT] [date] NULL, [CLM_ID] [varchar](20) NULL, [DTL_REC_NBR] [int] NULL, [ADJ_REC_NBR] [int] NULL, [PAID_DENIED_IND] [char](1) NULL )


    Nothing is Permanent... even Knowledge.... My Blog

    Tuesday, August 06, 2013 8:21 AM
  • Thanks to everyone for all the input. I must apologize for being such a dunce. I figured out what to do.

    Basically there can be any amount of medical claim information in this table per claim ID. The lines are considered as being part of the same "claim" if the CLM_ID, FROM_DT, and PAID_DENIED_IND are the same. Knowing this, I created a temp table with only those distinct fields with a row number:

    SELECT FROM_DT, CLM_ID, PAID_DENIED_IND, ROW_NUMBER() OVER (PARTITION BY CLM_ID ORDER BY CLM_ID, FROM_DT, PAID_DENIED_IND) AS Row_Num
    INTO My_Table_Temp
    FROM 
    (	
    	SELECT DISTINCT FROM_DT, CLM_ID, PAID_DENIED_IND
    	FROM My_Table
    ) AS T

    I then indexed the temp table and joined it to the original My_Table on the FROM_DT, PAID_DENIED_IND, and CLM_ID. This gave me the ROW_NUMBER() I was looking for across the claims.

    Thanks to everyone who replied.

    Tuesday, August 06, 2013 1:54 PM