How to filter records by the nearest date?

Question

Oracle

Hello! Please help me figure it out.
I have the following data model.
models.py:

class Securities(models.Model):
    section = models.ForeignKey(Section)
    characteristic = models.ForeignKey(Characteristics)
    bool_value = models.NullBooleanField()
    change_date = models.DateTimeField()
    real_change_date = models.DateTimeField()

For example, the table has the following entries:

SECTION  | CHARACTERISTIC | CHANGE_DATE | BOOL_VALUE
   A     |       8328     |  15.02.2018 |      1
   A     |       8328     |  02.09.2018 |      0
   B     |       8328     |  02.09.2018 |      1
   C     |       8328     |  02.09.2018 |      1
   C     |       8328     |  20.09.2018 |      0

I'm trying to filter a table like this:

SECTION  | CHARACTERISTIC | CHANGE_DATE | BOOL_VALUE
   C     |       8328     |  20.09.2018 |      0
   B     |       8328     |  02.09.2018 |      1
   A     |       8328     |  02.09.2018 |      0

That is, filter the records in such a way that only the one with the closest date is taken from similar records.
I tried to do the following:

securities = Securities.objects.filter(characteristic=8328).order_by('-change_date').values_list('section').distinct()

This code returns 36 records and the records are still duplicated. That is, there are two records A. Although you need to leave one. At the same time, when I write print(securities.count())to me, the number 33 is written. What is happening in general. How to solve this problem, friends?

Answer 1

select distinct on (section)
  section,
  characteristic,
  change_date,
  bool_value
from securities
where characteristic = 8328
group by id, section
order by section desc, change_date desc

section | characteristic | change_date | bool_value
---------+----------------+---------------------- ------+-----------
c | 8328 | 2018-09-20 12:28:00.701+00 | f
b | 8328 | 2018-09-02 12:28:00.701+00 | ta
| 8328 | 2018-09-02 12:27:41.907+00 | f
(3 rows)
well, respectively, if you need only sections

select distinct on (section)
  section
from accounts_securities
where characteristic = 8328
group by id, section
order by section desc, change_date desc

ps. Damn, right now I noticed that you have Oracle in your tags, I checked it under postgris.

Answer 2

On sensations, the correlated subquery will rescue. Something like this:
SELECT * FROM securities t
WHERE t.change_date IN (SELECT Max(t1.change_date) FROM securities t1 WHERE t.section=t1.section)
If it works for a long time, then I suspect that you can come up with something more productive using analytic functions...

Answer 3

select
  section
 ,characteristic
 ,change_date
 ,bool_value
  from (
    select
      section
     ,characteristic
     ,change_date
     ,bool_value
     ,row_number() over (partition by section, characteristic order by change_date desc) as rn
  )
 where rn = 1

Sorry it's late, but let it be here.