[Logo]
Forums Register Login
Examples of Partition, Row Over to find duplicate queries

-- Here i wanted to find out rows that have duplicate values in 2 columns, and find out the lesser id on these 2 rows.
select * from (SELECT ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY colid DESC) as row_number,
colid, col1, col2, col3, col4 from <some table> ) where row_number = 2;


-- here i wanted to find out latest bonds records a history table that tracks various status that a bond can have.
select a.bond_key, a.bond_status_tp_cd, a.status_date, a.maximum_date from
(select bond_key, bond_status_tp_cd, status_date, max(status_date)
OVER (PARTITION BY bond_key) as maximum_date from oltp.bond_status_history) a ,
oltp.bond b where b.bond_key = a.bond_key and a.status_date = a.maximum_date and b.IBL_CREATN_IND = 'Y' and a.bond_status_tp_cd in ('25', '100','110','109');


We can walk to school together. And we can both read this tiny ad:
ScroogeXHTML 7.1 - RTF to HTML5 / XHTML converter
https://coderanch.com/t/690611/ScroogeXHTML-RTF-HTML-XHTML-converter


This thread has been viewed 395 times.

All times above are in ranch (not your local) time.
The current ranch time is
Feb 20, 2018 00:51:23.