Queries for the latest id with the same titles

Question

I am new to Oracle and SQL. As I have started learning on my own with database query programming, I am looking for a tip or solution to my problem:

I created myself a random database with order tickets for different products. In my advancedorders table I have a detailed breakdown of what has been changed at any given time.

Below I present the situation:

enter image description here

I would like to construct the database query so that it searches only for the oldest records from a given group by ID:

enter image description here

And get something like this:

enter image description here

I have tried this query:

SELECT *
FROM database.advancedorders
INNER JOIN (
  SELECT
    TICKET_ID,
    max(id) as maxId
    from database.advancedorders
    group by TICKET_ID
) groupedTable
ON advancedorders.id = groupedTable.maxId
and advancedorders.TICKET_ID = groupedTable.TICKET_ID

However, I am not getting this query... Can someone please advise me?

Answer 1

Here's one option; read comments within code (sample data in lines #1 - 16, so query you might be interested in begins at line #17):

SQL> with test (id, ticket_id, title) as
  2    (select 1, 2345009, 'Banana' from dual union all
  3     select 2, 2345009, 'Banana' from dual union all
  4     select 3, 2345009, 'Apple' from dual union all
  5     select 4, 2345009, 'Apple' from dual union all
  6     --
  7     select 5, 4535003, 'Lemon' from dual union all
  8     select 5, 4535003, 'Lemon' from dual union all
  9     select 6, 4535003, 'Lemon' from dual union all
 10     --
 11     select 7, 3350001, 'Pear' from dual union all
 12     select 8, 3350001, 'Pear' from dual union all
 13     select 9, 3350001, null from dual union all
 14     --
 15     select 10, 4429005, 'Watermelon' from dual
 16    ),

---

 17  temp as
 18    -- rank them in descending order
 19    (select id, ticket_id, title,
 20       row_number() over (partition by ticket_id order by id desc) rn
 21     from test
 22    )
 23  -- finally, return those that ranked the "highest" (rn = 1)
 24  select id, ticket_id, title
 25  from temp
 26  where rn = 1;

        ID  TICKET_ID TITLE
---------- ---------- ----------
         4    2345009 Apple
         9    3350001
        10    4429005 Watermelon
         6    4535003 Lemon

SQL>