Filter records where row_number() is 1-CodePudding

select 
    account_num,load_date, xyz, abc, pqr
    row_number() over(partition by load_date desc) as rn
from 
    balance,
where 
    tran_code = 123

I am getting o/p of this code. Now I want to extract those rows where rn = 1. Can anyone help me with this?

CodePudding user response：

Note that on some databases (e.g. Teradata, BigQuery), we can avoid a formal subquery and instead use the QUALIFY clause:

SELECT account_num, load_date, xyz, abc, pqr
FROM balance
WHERE tran_code = 123
QUALIFY ROW_NUMBER() OVER (PARTITION load_date ORDER BY <col>) = 1;

On SQL Server, we can avoid a subquery using a TOP 1 WITH TIES trick:

SELECT TOP 1 WITH TIES account_num, load_date, xyz, abc, pqr
FROM balance
WHERE tran_code = 123
ORDER BY ROW_NUMBER() OVER (PARTITION load_date ORDER BY <col>);

Your calls to ROW_NUMBER should probably have an ORDER BY clause to make sense.

CodePudding user response：

Using a common table expression:

with b as ( 
   select account_num, load_date, xyz, abc, pqr
      row_number() over(partition by load_date order by load_date desc) as rn
    from balance
    where tran_code = 123
)
select account_num, load_date, xyz, abc, pqr
from b
where rn=1;