how to select the particular records with filter conditions when duplicates found in combination of two columns?
You'll need the RANK function (here is documentation, assuming you're using Oracle). You basically group the records (normally you'd do that with GROUP BY, here it's the PARTITION BY clause) and rank/order them within that group. Then, you select the records with rank 1:

SELECT s.no FROM (
  SELECT s.no, RANK() OVER (PARTITION BY name, last_name
                            ORDER BY status ASC, join_date DESC) AS rank
    FROM school
) temp WHERE rank = 1


(I'm not really familiar with Oracle, I hope this compiles; it would work on sql-server.)