1

My Table:

create table temp_table_name 
(seq number(10), start_Date date, end_date date, param varchar2(10))

My PK:

alter table temp_table_name add primary key (seq,start_date,end_Date)

My Records:

SEQ START_DATE  END_DATE    PARAM
10  01-Jan-2017 01-Jan-2020 10
10  01-Jan-2017 01-Jan-2022 20
10  05-Jan-2017 01-Jan-2022 30
10  06-Jan-2017 01-Jan-2020 25
10  06-Jan-2017 01-Jan-2021 50

My Requirement

To Find the record with the MAX(START_DATE) & Among those records with the MAX(END_DATE)

My Try

Select M1.* From Temp_Table_Name M1,
(
    With x as (
    Select T1.SEQ,T1.START_DATE,T1.END_DATE
    From 
    Temp_Table_Name T1 Left Outer Join Temp_Table_Name T2
    On T1.Seq = T2.Seq 
    And T1.Start_Date < T2.Start_Date
    Where T2.SEQ is null)
    Select X1.SEQ,X1.START_DATE,X1.END_DATE
    From
    X X1 Left Outer Join X X2
    On X1.Seq = X2.Seq
    And X1.End_Date < X2.End_Date
    Where X2.Seq is null
) M2
Where M1.Seq = M2.Seq
And M1.START_DATE = M2.Start_Date
And M1.End_Date = M2.End_Date

Although it fetches the records how i want, the query doesn't look that great & i doubt its the most efficient way to do this. I also would like to know if there might be any issues in the way I am trying to do this in future in a scenario i have not encountered yet.

Found the idea to create my query from Here.

Edit:

Another way of doing it with a much more elegant query:

Select * From (
    Select t1.*,
    row_number() over (partition by seq order by start_date desc, end_Date desc) rank 
    From temp_table_Name T1
)Where rank = 1
pOrinG
  • 896
  • 3
  • 13
  • 27
  • 1
    The second query is the proper way to do it. The first query looks a bit queer. One reason is that you are using anti joins rather than the straight-forward `NOT EXISTS`. This is a technique to circumvent issues in DBMS with a poor optimizer. Not necessary in Oracle hence. The other reason is that you select records with `max(start_date)` then from these those with `max(end_date)`, but then you superfluously join with the table again (`M1`), suddenly using the outdated 1980s join syntax even. Anyway both queries work. – Thorsten Kettner Oct 08 '17 at 09:27
  • @ThorstenKettner Thanks for the comment. I am also going forward with the second query. I started working in oracle on an application which always used the old '(+)' for outer joins & where condition for equi join hence it always finds its way into my queries. Its just an outdated syntax but it works just fine even today. – pOrinG Oct 08 '17 at 09:41

0 Answers0