Return max row number on every row?

shazma 0
Viewed 32

I have part of a query here:

ROW_NUMBER() OVER(PARTITION BY ddr.number ORDER BY ddr.date) row_number,

To return the row number on each row as below

| date | ddr_number | number | | |------------|----------------------------------|-----------|---| | 05/09/2022 | HMCJCKIB5T6EF63RGLH1R60N2400QBV9 | 757824084 | 1 | | 05/09/2022 | HMCJCKIB5T6EF63RGLH1R60N2400QBV9 | 757824084 | 2 | | 05/09/2022 | HMCJCKIB5T6EF63RGLH1R60N2400QBV9 | 757824084 | 3 | | 05/09/2022 | HMCJCKIB5T6EF63RGLH1R60N2400QBV9 | 757824084 | 4 | | 05/09/2022 | HMCJCKIB5T6EF63RGLH1R60N2400QBV9 | 757824084 | 5 |

How could I adjust the query to return the following:

date ddr_number number Row Number
05/09/2022 HMCJCKIB5T6EF63RGLH1R60N2400QBV9 757824084 5
05/09/2022 HMCJCKIB5T6EF63RGLH1R60N2400QBV9 757824084 5
05/09/2022 HMCJCKIB5T6EF63RGLH1R60N2400QBV9 757824084 5
05/09/2022 HMCJCKIB5T6EF63RGLH1R60N2400QBV9 757824084 5
05/09/2022 HMCJCKIB5T6EF63RGLH1R60N2400QBV9 757824084 5

I want to return the max row number on every row, how can I do this without selecting max from a subquery etc, is there a way to do this by simply adjusting the window function?

Thanks!

sql postgresql
1 Answers

If I understood you correctly you just need to count number of rows per 'number'. If Yes, consider the following approach:

WITH ddr AS (
    SELECT '05/09/2022' AS date, 'HMCJCKIB5T6EF63RGLH1R60N2400QBV9' AS ddr_number, '757824084' AS number UNION ALL
    SELECT '05/09/2022' AS date, 'HMCJCKIB5T6EF63RGLH1R60N2400QBV9' AS ddr_number, '757824084' AS number UNION ALL
    SELECT '05/09/2022' AS date, 'HMCJCKIB5T6EF63RGLH1R60N2400QBV9' AS ddr_number, '757824084' AS number UNION ALL
    SELECT '05/09/2022' AS date, 'HMCJCKIB5T6EF63RGLH1R60N2400QBV9' AS ddr_number, '757824084' AS number UNION ALL
    SELECT '05/09/2022' AS date, 'HMCJCKIB5T6EF63RGLH1R60N2400QBV9' AS ddr_number, '757824084' AS number UNION ALL
    SELECT '05/10/2022' AS date, 'HMCJCKIB5T6EF63RGLH1R60N2400QBV9' AS ddr_number, '757824085' AS number UNION ALL
    SELECT '05/10/2022' AS date, 'HMCJCKIB5T6EF63RGLH1R60N2400QBV9' AS ddr_number, '757824085' AS number UNION ALL
    SELECT '05/10/2022' AS date, 'HMCJCKIB5T6EF63RGLH1R60N2400QBV9' AS ddr_number, '757824085' AS number
)
SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY ddr.number ORDER BY ddr.date) AS row_number,
    COUNT(1) OVER (PARTITION BY ddr.number) AS number_of_rows_per_number
FROM
    ddr

Here is the output:

Counting number of rows per partition

stantyan0
Related