SQL Server : select distinct until the value is changed

I have a table of tasks (each task can has a few rows) that looks something like this:

TaskID | DateCreated | Status
-------+-------------+------------
  1    | 01-01-2017  | Started
  2    | 02-02-2017  | InProgress
  1    | 02-02-2017  | Started
  1    | 03-03-2017  | InProgress
  2    | 03-03-2017  | InProgress
  1    | 04-04-2017  | InProgress
  2    | 04-04-2017  | Done
  1    | 05-05-2017  | Done

I have more columns which are not relevant for this requirement.

  • Using CTE to update multiple columns in table and loop on all rows in table
  • SQL Server 2012 Dialog Boxes unreadable on Windows 10
  • Eliminating Special Characters From a SQL Server Query
  • SQL Server Reference a Calculated Column
  • Creating a database diagram crashes SSMS with a memory corruption error
  • SQL Server dynamic pivot returning null value when none exist in the data
  • What I need here is to select the actual status flow that a specific task has passed.

    For example, for task with TaskID = 1, I want to get the following result:

    TaskID | Status
    -------+-------------
      1    | Started
      1    | InProgress
      1    | Done
    

    Notes:

    1. The rows may be not sorted

    2. A status can repeat itself, e.g. there can be a task that its’ status flow was:

      Started ==> InProgress ==> Break ==> InProgress ==> Done
      

      so will not help me to get for each task the unique status from each status type it passed

    3 Solutions collect form web for “SQL Server : select distinct until the value is changed”

    In SQL 2012+ you could use LAG

      DECLARE @SampleData AS TABLE
      (
        TaskID int,
        DateCreated date,
        Status varchar(20)
      )
    
      INSERT INTO @SampleData
      VALUES
      (1, '2017-01-01', 'Started'),
      (2, '2017-02-02', 'InProgress'),
      (1, '2017-02-02', 'Started'),
      (1, '2017-03-03', 'InProgress'),
      (2, '2017-03-03', 'InProgress'),
      (1, '2017-04-04', 'InProgress'),
      (2, '2017-04-04', 'Done'),
      (1, '2017-05-05', 'Done')
    
      ;with temp as
      (
         SELECT  *, 
                 lag(sd.Status,1,'') OVER(PARTITION BY sd.TaskID ORDER BY sd.DateCreated)  AS PreviousRowStatus 
         FROM @SampleData sd
      )
      SELECT t.TaskID, t.DateCreated, t.Status
      FROM temp t
      WHERE t.Status != t.PreviousRowStatus
      ORDER BY t.TaskID
    

    Returns

    TaskID  DateCreated Status
    --------------------------
    1       2017-01-01  Started
    1       2017-03-03  InProgress
    1       2017-05-05  Done
    2       2017-02-02  InProgress
    2       2017-04-04  Done
    

    For older version you could use Row_number and APPLY

    Below is the query where in partition by will fetch you the desired result.

    select taskid,status(select taskid,status
    ,row_number()over(partition by taskid,status order by taskid)rownum
    from [yourtablename])tmp
    where rownum = 1
    

    Try Query (select distinct ):

    SELECT DISTINCT TaskID, Status
    FROM            (SELECT   TaskID, Status
                              FROM            task
                              ORDER BY TaskID) AS task_1
    WHERE        (TaskID = 1)
    ORDER BY Status DESC
    

    Or Simply :

    SELECT DISTINCT TaskID, Status
    FROM            task
    WHERE        (TaskID = 1)
    ORDER BY TaskID, Status DESC
    

    Result for your demo content:

    enter image description here

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.