SQL : select the oldest record for each distinct element

Question

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.

You may have a look at this similar question: https://stackoverflow.com/questions/18725168/sql-group-by-minimum-value-in-one-field-while-selecting-distinct-rows — mrfujin, Feb 18 '19 at 22:46

score 0 · Accepted Answer · answered Feb 18 '19 at 22:30

0

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';

answered Feb 18 '19 at 22:30

Derviş Kayımbaşıoğlu

28,492
4
50
72

1

You did exactly what I asked, and taught me a very interesting use of aliases. You have all my thanks ! – Cynass Feb 18 '19 at 23:23

score 0 · Answer 2 · answered Feb 18 '19 at 22:34

0

You only need to group by task_id and get the minimum date_change:

select task_id, 'Green' task_status, min(date_change) date_change
from logs
where task_status = 'Green'
group by task_id

For a specific task_id = '101' you don't need the grouping:

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

answered Feb 18 '19 at 22:34

forpas

160,666
10
38
76

Thank you very much for your feedback ! Your solution worked very well, but I'm not sure how to apply it when a column that doesn't perfectly group is added (see my edited post), is there something to be done ? Again, thank you – Cynass Feb 18 '19 at 23:16

score 0 · Answer 3 · answered Feb 19 '19 at 01:02

In Oracle, this can be achieved very efficiently using analytic functions, aka window functions. For this use case, ROW_NUMBER() is your friend :

SELECT * 
FROM (
    SELECT 
        l.*, 
        ROW_NUMBER() OVER(PARTITION BY task_id ORDER BY date_change) rn
    FROM logs l
    WHERE ASK_STATUS = 'Green'
) x WHERE rn = 1

The inner query assigns a number to each record in groups having the same task_id and task_status, with the earliest record having number 1. You can run the subquery to see the results. Then, the outer query filters in the first record in each group.

Window functions are available in Oracle since early versions (8i). They usually beat aggregation and subqueries in terms of efficiency.

SQL : select the oldest record for each distinct element

3 Answers3