CODEWITHSUNDEEP
sqlnetezza
arcee123
arcee123

Reputation: 211

How to get MAX rownumber with rowNumber in same query

I have ROW_NUMBER() OVER (ORDER BY NULL) rnum in a sql statement to give me row numbers. is there a way to attach the max rnum to the same dataset going out?

what I want is the row_number() which I get, but I also want the MAXIMUM rownumber of the total return on each record. 

    SELECT 
    ROW_NUMBER() OVER (ORDER BY NULL) rnum,
    C.ID, C.FIELD1, C."NAME", C.FIELD2, C.FIELD3
    FROM SCHEMA.TABLE 
    WHERE (C.IS_CRNT = 1)
), MAX_NUM as (
    SELECT DATA.ID, max(rnum) as maxrnum from DATA GROUP BY DATA.COMPONENT_ID
) select maxrnum, DATA.* from DATA JOIN MAX_NUM on DATA.COMPONENT_ID = MAX_NUM.COMPONENT_ID

DESIRED RESULT (ASSUMING 15 records):

1     15    DATA
2     15    DATA
3     15    DATA
etc...

Upvotes: 0

Views: 1316

Answers (2)

Sean
Sean

Reputation: 573

Based on my assumptions in your dataset, this is the approach I would take:

WITH CTE AS (
select C.ID, C.FIELD1, C."NAME", C.FIELD2, C.FIELD3, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID)
FROM SCHEMA.TABLE WHERE (C.IS_CRNT = 1))
SELECT *, (select count(*) from cte) "count" from cte;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I think you want count(*) as a window function:

SELECT ROW_NUMBER() OVER (ORDER BY NULL) as rnum,
       COUNT(*) OVER () as cnt,
       C.ID, C.FIELD1, C."NAME", C.FIELD2, C.FIELD3
FROM SCHEMA.TABLE 
WHERE C.IS_CRNT = 1

Upvotes: 3

Related Questions