ROW_NUMBER() as a CASE input

Question

CASE is wonky when wrapping around a nested select statement using ROW_NUM() as the comparable.

SELECT
    *,
    CASE 
        WHEN rownum = 1 THEN 1
        ELSE 0
    END AS col3
FROM
    (SELECT
         col1, col2,
         ROW_NUMBER() OVER (PARTITION BY col2 ORDER BY col1) AS rownum
     FROM
         table
    )

I expected that the first row within the partition is 1, and every other row is 0. But when i tried did, there is only ONE row that's 1, and everything else is 0. however, the rownum still returns correctly.

But my query results looks like this

col1	col2	ronum	col3
1	a	1	0
2	a	2	0
3	a	3	0
1	b	1	0
1	c	1	1
1	d	1	0
1	e	1	0

Answer 1

In Oracle, rownum is a special column (pseudocolumn) that returns its own row number which is throw off the case statement.

Rename rownum to any other name, ie row_num.

SELECT 
  t.*,
  CASE WHEN t.row_num = 1
  THEN 1
  ELSE 0
  END AS col3

FROM (
  SELECT
    col1,
    col2,
    ROW_NUMBER() OVER (PARTITION BY col2 ORDER BY col1) as row_num
  FROM test
) t;