2

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?

1
  • Are you using SQL Server or Oracle? Please be careful which tags you use and hover over them to read the description.
    – Glorfindel
    Apr 30 '20 at 7:27
2

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

1

This answer is one more way to achieve the objective however answer provided by Glorfindel is correct and efficient.

Its always great to have DDL and some minimum reproducible code for one to answer. Below is the DDL of underlying table:

CREATE TABLE [dbo].[Test_SE](
    [S NO] [smallint] NULL,
    [NAME] [nchar](20) NULL,
    [LAST NAME] [nchar](10) NULL,
    [JOIN DATE] [date] NULL,
    [SUBJECT] [nchar](10) NULL,
    [STATUS] [nchar](10) NULL,
    [MARK] [smallint] NULL
) 
GO

After doing insert into above table:

Result Set

You might use below piece of code:

select NAME + [LAST NAME] from Test_SE
group by NAME + [LAST NAME]
having count(*) = 1
union
select NAME + [LAST NAME] from Test_SE A
where STATUS = 'Active'
and [JOIN DATE] = (select max( [JOIN DATE]) from Test_SE B where B.[S NO] = A.[S NO])
group by NAME + [LAST NAME]
having count(*) > 1

Hope this helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.