SQL : select the oldest record for each distinct element

I apologize for the poor worded title, let me illustrate first

Here is the table :

select task_id, task_status, date_change, username from logs
order by task_id, date_change

Results :

 TASK_ID    TASK_STATUS DATE_CHANGE     USERNAME    

1   101     Green       2019/01/03      Camille 
2   101     Blue        2019/01/07      Lucas
3   101     Green       2019/01/09      Rudy

4   102     Blue        2019/01/03      Lucas
5   102     Green       2019/01/04      Delphine

6   103     Yellow      2019/01/07      Penelope
7   103     Green       2019/01/11      Rudy
8   103     Blue        2019/01/14      Delphine
9   103     Green       2019/01/18      Camille

10  104     Blue        2019/01/08      Rudy
11  104     Green       2019/01/10      Camille
12  104     Green       2019/01/14      Penelope

I have several tasks, each of which can have a different status. For every tasks, I want to extract the row with the earliest green status.

So in this case the result would be :

    TASK_ID TASK_STATUS DATE_CHANGE     USERNAME
1   101     Green       2019/01/03      Camille 
2   102     Green       2019/01/04      Delphine
3   103     Green       2019/01/11      Rudy
4   104     Green       2019/01/10      Camille

Here is the closest i’ve got to a solution :

select task_id, task_status, date_change, username
from logs
where task_status =('Green')
and task_id = ('101')
and date_change = (
    select min(date_change)
    from logs 
    where task_status = ('Green') and task_id =('101')
)

It is not good at all, I have to do a query for every single task, totally unpractical.

Is there a way to use a kind of variable and change the last line by something like :

and date_change = (
    select min(date_change)
    from logs 
    where task_status = ('Green') and task_id =($CURRENT_TASK_ID)
)

Or maybe another way altogether to solve that problem ?

I greatly thank you for you time.

Answer

Try this:

select * from logs l
where Date_Change = (
    select min(DATE_CHANGE) from logs li 
    where li.task_id = l.task_id and li.task_Status = l.task_status )
  and TASK_STATUS = 'Green';

Advertisement

Answer