You can use the analytic ROW_NUMBER function.
I don't have a copy of your table, so I'll use scott.emp to illustrate. In scott.emp, there may be multiple rows for a single job. To display just 1 row per job, the row with the most recent hiredate:
WITH got_r_num AS
SELECT empno, job, deptno, hiredate -- Or whatever columns you want
, ROW_NUMBER () OVER ( PARTITION BY job
ORDER BY hiredate DESC
) AS r_num
-- WHERE ... -- If you need any filtering put it here
SELECT * -- Or list all columns except r_num
WHERE r_num = 1
What results do you want in case of ties? Depending on your requirements, you may want to add tie-breaking expressions to the analytic ORDER BY clause, and/or use RANK instead of ROW_NUMBER.
I hope this answers your question.
If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
Point out where the query above is producing the wrong results, and explain, using specific examples, how you get the right results from the given data in those places.
If you modify the query at all, post your modified version.
Always say what version of Oracle you're using (e.g. 188.8.131.52.0).
See the forum FAQ: https://forums.oracle.com/message/9362002
Here I dont want to use the group by and nested select in from clause.