locked
autonumbering not every row RRS feed

  • Question

  • Hi,

    I want to autonumber only rows with specific value in one column (in other words I do not want to autonumber rows with specific value in the column). How can I achieve such "filter" on autonumbering?

    Thanks,

    Marcin

    Thursday, September 13, 2012 5:24 PM

Answers

  • Apologies, there are a few syntax errors.  Here's a complete code example to show it working:

    with data as (
    select 'a' as letter
    union
    select 'b'
    union
    select 'c'
    union 
    select 'd'
    )
    
    select	
    	letter, 
    	case when  letter <> 'c' then row_number() OVER (order by case when letter = 'c' then 1 else 0 end, letter)else null end AS RowNumber,
    	case when letter = 'c' then 1 else 0 end as filterColumn
    from
    	data
    order by
    	case when letter = 'c' then 1 else 0 end,
    	letter


    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    • Proposed as answer by Syed Qazafi Anjum Thursday, September 13, 2012 9:30 PM
    • Marked as answer by Charlie Liao Thursday, September 20, 2012 6:13 AM
    Thursday, September 13, 2012 9:25 PM

All replies

  • Hmm, I can think of one way, but it's not very elegant.

    SELECT
    *, 
    case when filter_column = 'undesired value' then 1 else 0 end AS FilterOrder, 
    case when filter_colmm <> 'undesired value' then row_number() else null end AS RowNumber
    FROM
    DATA
    ORDER BY
    case when filter_column = 'undesired value' then 1 else 0 end asc,
    <any other ordering you need>


    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    Thursday, September 13, 2012 6:37 PM
  • Apologies, there are a few syntax errors.  Here's a complete code example to show it working:

    with data as (
    select 'a' as letter
    union
    select 'b'
    union
    select 'c'
    union 
    select 'd'
    )
    
    select	
    	letter, 
    	case when  letter <> 'c' then row_number() OVER (order by case when letter = 'c' then 1 else 0 end, letter)else null end AS RowNumber,
    	case when letter = 'c' then 1 else 0 end as filterColumn
    from
    	data
    order by
    	case when letter = 'c' then 1 else 0 end,
    	letter


    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    • Proposed as answer by Syed Qazafi Anjum Thursday, September 13, 2012 9:30 PM
    • Marked as answer by Charlie Liao Thursday, September 20, 2012 6:13 AM
    Thursday, September 13, 2012 9:25 PM