How can I retrieve the oldest record from a joined table in SQL with distinct values for a specific column and a certain condition?

Question

I want to get distinct vale for column "ID" with condition "Status"=1 and pick the oldest "Date".

There are 2 tables:Table1

| ID       | Code     | Date     |
|--------- | ---------|----------|
|A123      |1234      |10/9/2018 |
|A123      |7890      |9/8/2022  |
|B666      |9088      |1/2/2021  |
|C7633     |1048      |2/5/2019  |
|A123      |5477      |8/3/2022  |

Table2

| Code   | Status   | 
|------- | ---------|
|123     |1         |
|7890    |2         |
|9088    |1         |
|1048    |1         |
|5477    |1         |

I tried below sql, but it show 2 record of "ID" = A123 with "Status=1".

Select table1.ID, table1.Code, Min(table1.Date),table2.Status From table1, table2 Where table1.Code=table2.Code and table2.Status = '1' Group by table1.ID, table1.Code, table2.Status;

My expected result is

ID	Code	Date	Status
A123	1234	10/9/2018	1
B666	9088	1/2/2021	1
C7633	1048	2/5/2019	1

Answer 1

Try how the result looks like with below SQL, as you expect distinct "ID", then just put "ID" after "Group by"

Select t1.ID,
Min(t1.Code) min_code,
Min(t1.Date) min_date,
Min(t2.Status) min_status 
From table1 t1, table2 t2
Where t1.Code=t2.Code 
and t2.Status = '1' 
Group by t1.ID;