Aiden Aiden - 24 days ago 5
SQL Question

Getting oldest Date SQL Complexity

I have a problem which I cannot resolve no matter what without using code, instead of SQL SCRIPT.

I have 2 tables

Person
ID Name Type
1 A A1
2 B A2
3 C A3
4 D A4
5 E A6


PersonHomes

HOMEID Location PurchaseDate PersonID
1 CA 20160101 1
2 CT 20160202 1
3 DT 20160101 2
4 BT 20170102 3
5 CT 20160303 1
6 CA 20160101 2


PersonID is foreign key of Person Table

There are no other rowz in the tables

So, we have to show detail of EACH person WITH home

The rule to write output is

IF Person has SINGLE entry in PersonHomes then use it

IF Person has MORE than ONE entry in PersonHomes then we have to look at purchase date, IF they are different then USE the PersonHomes ROW with OLDEST date in it. AND DELETE OTHER ROWS OF HIM

IF Person has MORE than ONE entry in PersonHomes then we have to look at purchase date, and IF DATES are SAME then USE the ROW with LOWER ID AND DELETE THE OTHER ROWS of HIM


This is very easy to do in code but using SQL it is complex

What I tried was to

WITH PERSON (
SELECT * FROM Person)

SELECT * FROM PERSON
INNER JOIN PersonHomes ON Person.ID = PersonHomes.PersonID
WHERE PersonHomes.PersonID = CASE WHEN (COUNT (*) FROM PersonHomes...)

Then I think I can write SQL function ?

I am stuck, Please help!

SAMPLE OUTPUT for PERSON A

ID NAME Type HOMEID Location PurchaseDate

1 A A1 5 CT 20160303


For PERSON B

ID NAME Type HOMEID Location PurchaseDate

1 A A2 3 DT 20160101


Aiden

Answer

You seem to have a prioritization query. I would solve this using row_number():

select ph.*
from (select ph.*,
             row_number() over (partition by personid
                                order by purchasedate asc, homeid asc
                               ) as seqnum
      from personhomes ph
     ) ph
where seqnum = 1;

This doesn't actually change the data in the table. Although you say delete, it seems like you just want a result set with one home per person.

Comments