0

I need to extract one record from column Y where in column date has the last date

example

id Y DATE
a 1 2020
a 2 2021
a 2 2022
b 1 1999
b 1 2015
c 3 2001
c 3 2004
c 7 2010
Jonas Metzler
  • 4,517
  • 1
  • 5
  • 17
SQL_Python_DataScience
  • 69
  • 6
  • "I need" is no question. You are missing to tell us what you have tried and what exactly doesn't work as intended. Is this sample data already the whole use case which should be covered? This would even not require any subquery or other "complicated" things, but can be done with simple MAX and GROUP BY. – Jonas Metzler Jan 13 '23 at 06:56
  • Does this answer your question? [Oracle SQL query: Retrieve latest values per group based on time](https://stackoverflow.com/questions/2000908/oracle-sql-query-retrieve-latest-values-per-group-based-on-time) Or [Return row with the max value of one column per group](https://stackoverflow.com/questions/10342405/return-row-with-the-max-value-of-one-column-per-group) – astentx Jan 13 '23 at 07:20

1 Answers1

2

One option is to rank rows per each id sorted by years in descending order, and then fetch the ones that ranked as the highest.

Sample data:

SQL> with
  2  test (id, y, datum) as
  3    (select 'a', 1, 2020 from dual union all
  4     select 'a', 2, 2021 from dual union all
  5     select 'a', 2, 2022 from dual union all
  6     select 'b', 1, 1999 from dual union all
  7     select 'b', 1, 2015 from dual union all
  8     select 'c', 3, 2001 from dual union all
  9     select 'c', 3, 2004 from dual union all
 10     select 'c', 7, 2010 from dual
 11    ),

Query:

 12  temp as
 13    (select id, y, datum,
 14       rank() over (partition by id order by datum desc) rnk
 15     from test
 16    )
 17  select id, y, datum
 18  from temp
 19  where rnk = 1;

ID          Y      DATUM
-- ---------- ----------
a           2       2022
b           1       2015
c           7       2010

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57