You are here

Oracel SQL, use ROW_NUMBER() to filter same records when JOIN tables

Sometimes we will retrieve repetitive records when we use JOIN to join tables.
Now distinct, group by, and Having will not work, ROW_NUMBER() is a good choice.

for example:

select a.one, b.two from A a
LEFT JOIN B b ON a.id = G.id where where a.name like '%abc%' and a.time < 1234545;

using ROW_NUMBER() after

SELECT ROWNUM, C* FROM(
SELECT a.id, a.one, b.two, b.id,
ROW_NUMBER() OVER(PARTITION BY a.id ORDER BY a.time DESC) AS ROW_FLG
FROM (select * from A where name like '%abc%' and time < 1234545) a
LEFT JOIN B b ON a.id=b.id
ORDER BY a.time DESC
) C WHERE ROW_FLG = '1';

Ref:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm
http://zjlszhugf.blog.163.com/blog/static/495008422011102552012529/

Blog: