Sql-server – how to select the particular records with filter conditions when duplicates found in combination of two columns

plsqlsql server

consider a table "SCHOOL" table, which contains the below data

enter image description here

when, duplicates found in NAME and LAST NAME combination it should look up for the STATUS which is "Active". if still it has duplicates, then look up for JOIN DATE which is most recent.

So the "SELECT" query should fetch S.NO 1,2,3,4,7.

S.NO 7 has ACTIVE status with most recent date

So could you please let me know the SQL Query to fetch these results?

Best Answer

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 .)

Related Question