How to query rows where only the rows with the highest value in a specific column appear?

Question

Sorry if my phrasing is confusing, self learning PL/SQL. I am trying to query all the columns in rows that have the highest value based on one column.

example: I have a table with three rows and three columns Table: PTest

Ptest_no | Test_id | Test_inst
------------------------------
ABC11    |    1    |     1
ABC11    |    2    |     1
ABC11    |    2    |     2

I need to get just the top and bottom row with all the columns it has (final table will have close to 10+ columns)

result:

ABC11    |    1    |     1
ABC11    |    2    |     2

I tried:

--but it only prints 3rd row.

select * from ptest
where test_inst = (select max(test_inst) from ptest);

--attempted self join thinking that a subquery could help specify the condition. --but only prints 3rd row

select a.Ptest_no, a.test_id, a.test_inst
from PTest a
join (select max(test_inst) as max_insty
    from PTest b
    where PTest_no = 'ABC11') on max_insty = a.test_inst
where PTest_no = 'ABC11';

--results in invalid relational operator. --I am unsure what that means.

select test_inst, ptest_no, test_id 
from ptest 
group by test_inst, ptest_no, test_id having max(test_inst);

Currently trying: -attempting again with self join but using CASE, having a hard time with CASE and unsure how to properly end it of if its best route. Commented out case and ran, prints only the 3rd row -added 4rd row names ptest_snu with value '69' on all rows. unsure why I did this.

select a.Ptest_no, a.test_id, a.test_inst, a.ptest_snu
from PTest a
--case 
   --when a.test_id = b.test_id then select max(test_inst)
   --else (select * from Ptest a) end
join (select max(test_inst) as max_insty
    from PTest b
    where PTest_no = 'ABC11') on max_insty = a.test_inst
where a.ptest_snu = '69';

Answer 1

I suspect that you want the row with the greatest test_inst for each test_id. If so, this is a greatest-n-per-group problem; one option is to filter with a correlated subquery:

select t.*
from ptest t
where t.test_inst = (
    select max(t1.test_inst) from ptest t1 where t1.test_id = t1.test_id
)

You can also use window functions:

select *
from (
    select t.*, row_number() over(partition by test_id order by test_inst desc) rn
    from ptest t
) t
where rn = 1