0

I have 2 table

select DISTINCT (B.MAIN_ID), A.NAME,  A.NAME_ID, A.CREATED_DATE 
 from FAMILY A, ADDRESS B where B.F_ID = A.N_ID

result:

MAIN_ID   |  Name     |  NAME_ID | CREATED_DATE 
100       |  Erica    |  254     | 20190808-01:02:01
100       |  Erica    |  245     | 20190808-00:01:01
300       |  Eden     |  331     | 20190808-00:03:34

I want to be remove duplicated rows 'MAIN_ID' by created date, I need last created record. How can I do this? thanks

MAIN_ID   |  Name     |  NAME_ID | CREATED_DATE 
100       |  Erica    |  254     | 20190808-01:02:01
300       |  Eden     |  331     | 20190808-00:03:34
  • Which one of Erica's rows do you remove - what is the removal criterion? p.s. welcome to the forum! :-) – Vérace Sep 12 '19 at 9:50
  • Why is the value 254? – Michael Kutz Sep 12 '19 at 9:51
  • Show source data which produces this output for this query. – Akina Sep 12 '19 at 9:52
  • 254 is the last created date – Solution Sep 12 '19 at 9:58
  • 3
    distinct is NOT a function. It always applies to all columns in the select list. Enclosing one column in parentheses won't change a thing. distinct (a),b,c is exactly the same thing as distinct a,(b),c or distinct a,b,(c) – a_horse_with_no_name Sep 12 '19 at 10:43
2

You can use ROW_NUMBER() ranking function and a subquery (derived table or CTE):

WITH
  cte AS
  ( SELECT
      B.MAIN_ID, A.NAME, A.NAME_ID, A.CREATED_DATE,
      ROW_NUMBER() OVER (PARTITION BY B.MAIN_ID
                         ORDER BY A.CREATED_DATE DESC)
        AS RN
    FROM
      FAMILY A JOIN ADDRESS B ON B.F_ID = A.N_ID
  )
SELECT MAIN_ID, NAME, NAME_ID, CREATED_DATE
FROM cte
WHERE RN = 1 ;
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.