CODEWITHSUNDEEP
sqloracle-database
Nico Schuck
Nico Schuck

Reputation: 982

Oracle Select column with number of distinct ID (similar to rownum)

I need a column which increases every time the id is different than in row before.

    id    desc     
    ---------------
    152   abcdedasd
    152   dasdawidh
    152   das ads a
    194   dasdas da
    168   aopwejapw
    168   dasidjasd

to:

    num   id    desc     
    ---------------------
    1     152   abcdedasd
    1     152   dasdawidh
    1     152   das ads a
    2     194   dasdas da
    3     168   aopwejapw
    3     168   dasidjasd

I have an Oracle 11g. Normally I need to use CASE or DECODE to Select columns which depend on other columns. But I need to refer to the previus line. And rownum increase on every line without condition.

Upvotes: 0

Views: 58

Answers (1)

MT0
MT0

Reputation: 168232

Use the DENSE_RANK analytic function:

Oracle Setup:

CREATE TABLE test_data ( id, "desc" ) AS
  SELECT 152, 'abcdedasd' FROM DUAL UNION ALL
  SELECT 152, 'dasdawidh' FROM DUAL UNION ALL
  SELECT 152, 'das ads a' FROM DUAL UNION ALL
  SELECT 194, 'dasdas da' FROM DUAL UNION ALL
  SELECT 168, 'aopwejapw' FROM DUAL UNION ALL
  SELECT 168, 'dasidjasd' FROM DUAL;

Query:

SELECT DENSE_RANK() OVER ( ORDER BY id ) AS num,
       t.*
FROM   test_data t

Output:

NUM |  ID | desc     
--: | --: | :--------
  1 | 152 | abcdedasd
  1 | 152 | dasdawidh
  1 | 152 | das ads a
  2 | 168 | aopwejapw
  2 | 168 | dasidjasd
  3 | 194 | dasdas da

db<>fiddle here

Upvotes: 4

Related Questions