SQL : select the oldest record for each distinct element

sql select oldest record in table
sql select most recent record for each id
sql select first record of each group
select distinct
sql select most recent date for each record
sql select most recent record for each user
sql select top 1 for each distinct
sql query to get latest record for all distinct items in a table

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.

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

Finding the Oldest/Youngest Records Within a Group, How to find the oldest or most recent record associated with a particular foreign key. This is straightforward in SQL. per holding ( holding_id and holding_type together ensure a unique StockHolding or BondHolding ). A common query that crops up in web apps is finding the oldest or the most recent record in a single table. This is straightforward in SQL. You can even write the relevant part of the query without knowing anything about the table (other than the fact that it has a timestamp column called created_at):

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'

SQL DISTINCT and TOP in Same Query, How do the SQL Distinct and SQL TOP SELECT modifiers Work Together in the same All the examples for this lesson are based on Microsoft SQL Server So, in this case, the statement will return distinct rows for FirstName and LastName. SQL HOME SQL Intro SQL Syntax SQL Select SQL Select Distinct SQL Where SQL And, Or, Not SQL Order By SQL Insert Into SQL Null Values SQL Update SQL Delete SQL Select Top SQL Min and Max SQL Count, Avg, Sum SQL Like SQL Wildcards SQL In SQL Between SQL Aliases SQL Joins SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Self Join SQL

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.

DISTINCT on one column and return TOP rows, Replace your dbname and schemaName in the following query. ;WITH CTE AS ( SELECT [Order_No] ,[Customer_Name] ,[Purchase_Cost] , ROW_NUMBER()� Output : Number of employees ----- 25 Pictorial Presentation: SQL COUNT( ) with All . In the following, we have discussed the usage of ALL clause with SQL COUNT() function to count only the non NULL value for the specified column within the argument.

How to select distinct for one column and any in another column , For other DBMSs, that have window functions (like Postgres, SQL-Server, The optional ORDER BY is for selecting which row from every group should be� It is easy to select the highest or lowest record in the database table with the MAX or MIN function. However, it’s a little bit tricky to select the n th highest record. For example, the get the second-most expensive product from the products table. To select the n th highest record, you need to perform the following steps:

How can I SELECT rows with MAX(Column value), DISTINCT by , Select BOTH the home and its max date time, then join back to the top table on BOTH the fields: Want to learn SQL from basics! Here's the right� SELECT DISTINCT ON (2) id, customer, total FROM purchases ORDER BY 2, 3 DESC, 1; If total can be NULL (won't hurt either way, but you'll want to match existing indexes): ORDER BY customer, total DESC NULLS LAST, id; Major points. DISTINCT ON is a PostgreSQL extension of the standard (where only DISTINCT on the whole SELECT list is defined).

SQL, Top-N Analysis in SQL deals with How to limit the number of rows returned from Input : SELECT ROWNUM as RANK, first_name, last_name, employee_id, salary the required fields are displayed for those 3 employees who were hired earliest. analytic function assigns a sequential rank to each distinct value in output. SQL HOME SQL Intro SQL Syntax SQL Select SQL Select Distinct SQL Where SQL And, Or, Not SQL Order By SQL Insert Into SQL Null Values SQL Update SQL Delete SQL Select Top SQL Min and Max SQL Count, Avg, Sum SQL Like SQL Wildcards SQL In SQL Between SQL Aliases SQL Joins SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Self Join SQL

Comments
  • You may have a look at this similar question: stackoverflow.com/questions/18725168/…
  • You did exactly what I asked, and taught me a very interesting use of aliases. You have all my thanks !
  • 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