Same Row_Number for Same Values

Question

I need to have row numbering where the ROW_NUMBER is the same for same value column: MFGPN (Same MFGPN will always be in sequence). I also need to maintain the original sequence by No.

Here's my table

No     MFGPN
1      Z363700Z01
2      Z363700Z01
3      0119-960-1
4      1A3F1-0503-01

I tried using RANK() to achieve the desired but am running into trouble.

SELECT RANK() OVER(ORDER BY MFGPN) As [Item], MFGPN FROM Table1 ORDER BY [No] ASC

RESULT

Item   MFGPN           Desired Result
3      Z363700Z01            1
3      Z363700Z01            1
1      0119-960-1            2
2      1A3F1-0503-01         3

Appreciate you guys' expert advise. Thanks!

Pரதீப் · Accepted Answer · 2017-01-24T06:39:40.237

21

Use DENSE_RANK window function instead of RANK. Rank will skip the sequence when there is a repetition of data Dense_Rank will not.

SELECT MFGPN,
        Dense_rank()OVER(ORDER BY m_no) as [Desired Result]
FROM   (SELECT no,
                MFGPN,
                Min(no)OVER(partition BY MFGPN) AS m_no
        FROM   (VALUES (1,'Z363700Z01' ),
                        (2,'Z363700Z01' ),
                        (3,'0119-960-1' ),
                        (4,'1A3F1-0503-01')) tc (no, MFGPN))a

If no is not unique then change DENSE_RANK to

Dense_rank()OVER(ORDER BY m_no,MFGPN)

Result :

+---------------+----------------+
|     MFGPN     | Desired Result |
+---------------+----------------+
| Z363700Z01    |              1 |
| Z363700Z01    |              1 |
| 0119-960-1    |              2 |
| 1A3F1-0503-01 |              3 |
+---------------+----------------+

edited Jan 24 '17 at 06:39

answered Jan 24 '17 at 06:14

Pரதீப்

91,748
19
131
172

Thanks for the quick response @Prdp. I don't get it though. I'm still getting 3, 3, 1, 2 with DENSE_RANK. I need it to be 1, 1, 2, 3 – Bobbit Jan 24 '17 at 06:20
@Bobbit - How you want to define the order ? Why `Z363700Z01` should be `1` and `1A3F1-0503-01` should be `3` ? – Pரதீப் Jan 24 '17 at 06:22
Order by `[No]`. Exactly as the table I presented with 'Desired Result' – Bobbit Jan 24 '17 at 06:25
`ORDER BY m_no` will be enough – David דודו Markovitz Jan 24 '17 at 06:35
@DuduMarkovitz - What if the `no` is repeated for another `MFGPN` – Pரதீப் Jan 24 '17 at 06:36
Seems like a unique value for the whole set and not per MFGPN and if it wasn't you couldn't use it to keep the order – David דודו Markovitz Jan 24 '17 at 06:38
@DuduMarkovitz - Initially even I didn't add `MFGPN` in `Dense_rank`. Updated my post accordingly – Pரதீப் Jan 24 '17 at 06:40
@Prdp - Works like a charm! Thanks! – Bobbit Jan 24 '17 at 06:52

score 1 · Answer 2 · answered Jan 24 '17 at 06:18

You should partition the results by mfgpn so that rows with the same mfgpn get the same rank and order by the no. Additionally, using dense_rank will ensure you don't "skip" any ranks:

SELECT   DENSE_RANK() OVER(PARTITION BY [mfgpn] ORDER BY [no]) As [Item], 
         [mfgpm] 
FROM     Table1 
ORDER BY [No] ASC

score 0 · Answer 3 · answered Jan 24 '17 at 06:25

0

select      sum(case when MFGPN = prev_MFGPN then 0 else 1 end) over (order by No)  as item
           ,MFGPN

from       (SELECT      lag(MFGPN) over (order by [No]) as prev_MFGPN   
                       ,[No]
                       ,MFGPN 
            FROM        Table1 
            ) t

ORDER BY    [No] ASC

+------+---------------+
| item | MFGPN         |
+------+---------------+
| 1    | Z363700Z01    |
+------+---------------+
| 1    | Z363700Z01    |
+------+---------------+
| 2    | 0119-960-1    |
+------+---------------+
| 3    | 1A3F1-0503-01 |
+------+---------------+

answered Jan 24 '17 at 06:25

David דודו Markovitz

42,900
6
64
88

This is what you have requested – David דודו Markovitz Jan 24 '17 at 06:27
`'lag' is not a recognized built-in function name.` I got this. Sorry I should've pointed out I'm using 2008 – Bobbit Jan 24 '17 at 06:32

score 0 · Answer 4 · answered Jan 24 '17 at 06:30

You can try below snippet. DENSE_RANK() a explained above s the best approach in this case.

    SELECT a.*,
      DENSE_RANK() OVER(ORDER BY MFGPN DESC) RN
    FROM
      (SELECT 1 AS no, 'Z363700Z01' AS mfgpn FROM dual
      UNION ALL
      SELECT 2 AS no, 'Z363700Z01' AS mfgpn FROM dual
      UNION ALL
      SELECT 3 AS no, '0119-960-1' AS mfgpn FROM dual
      UNION ALL
      SELECT 4 AS no, '1A3F1-0503-01' AS MFGPN FROM dual
      )a;

-------------------------------OUTPUT-------------------------------------------
NO  MFGPN           RN
1   Z363700Z01      1
2   Z363700Z01      1
4   1A3F1-0503-01   2
3   0119-960-1      3

--------------------------------------------------------------------------------

score 0 · Answer 5 · answered Jan 24 '17 at 06:54

TRY IT: I think it's more simple and flexible to use join and compare value to get the desired output as below:

SELECT p.MFGPN,
       Dense_rank()OVER(ORDER BY CASE WHEN p.MFGPN = tp.MFGPN THEN tp.num ELSE p.num END) AS [Desired Result]
FROM tmp_option p
LEFT JOIN tmp_option tp ON tp.num+1 = p.num
ORDER BY p.num ASC

Same Row_Number for Same Values

5 Answers5