-1

This is the code:

select security,max(dte),close,ask,bid,mid from k$prices
where to_char(dte,'MON-YYYY') = 'JAN-2021'
group by security,close,ask,bid,mid,dte
order by security,dte desc

Below is the result: I only want to get 2 rows, which has the highest date for each security (436 January 5 and 448 January 29) but because the values of the fields are different, all rows are still being shown. Please help me. Thanks

enter image description here

MT0
  • 143,790
  • 11
  • 59
  • 117
Benedict Solpico
  • 139
  • 1
  • 1
  • 10

1 Answers1

0

You could rank all your rows first within the inline view t, then select only those that have rank 1 ( rnb = 1)

select security, dte, close, ask, bid, mid
from (
  select security, dte, close, ask, bid, mid, row_number()over(partition by security order by dte desc) rnb
  from your_table
)t
where rnb = 1
;
Mahamoutou
  • 1,555
  • 1
  • 5
  • 11