Squashing values in a column by another timestamp colum

I have a following data which shows the status of a support ticket:


Edit:

More concise and generic example:

STATUS        SEQ_NO
New           1
Open          2
Open          3
Open          4
Queued        5
Open          6
Open          7
Open          8
Completed     9
Completed     10
Completed     11
Closed        12

From this, I would like to extract the records,

STATUS        SEQ_NO
New           1
Open          2
Queued        5
Open          6
Completed     9
Closed        12

Original question:
-- SELECT status, start_time FROM events_tab ORDER BY start_time;
STATUS        START_TIME
New           30/09/2014 3:48:10 PM    -- I want this record,
Open          30/09/2014 3:48:10 PM    -- and this,
Open          1/10/2014 10:41:57 AM
Open          4/03/2015 9:59:04 AM
Queued        18/06/2015 1:31:30 PM    -- and this,
Open          20/06/2015 10:10:47 PM   -- and this,
Open          20/06/2015 11:20:11 PM
Open          27/06/2015 1:18:50 PM
Completed     27/06/2015 1:22:08 PM    -- and this,
Completed     28/09/2015 9:31:55 AM
Completed     5/10/2015 11:57:38 AM
Closed        11/01/2016 9:31:26 AM    -- and this.

These are events that happened in each state. I want to make a timeline of state changes from it.

I want to squash these records such that only the very first row of a group is show. However, notice that there are actually two groups of Open status. So I should get two records with Open status.

Basically I want the following result:

STATUS        START_TIME
New           30/09/2014 3:48:10 PM
Open          30/09/2014 3:48:10 PM
Queued        18/06/2015 1:31:30 PM
Open          20/06/2015 10:10:47 PM
Completed     27/06/2015 1:22:08 PM
Closed        11/01/2016 9:31:26 AM

How can I achieve this with an SQL statement?

I have tried,

SELECT status, MIN(start_time)
FROM events_tab
GROUP BY status;

But this does not include multiple records in Open status, as my intention above.

You can use the Tabibitosan technique to achieve this goal:

WITH your_table AS (SELECT 'New' status, to_date('30/09/2014 03:48:10 PM', 'dd/mm/yyyy hh:mi:ss AM') start_time FROM dual UNION ALL
                    SELECT 'Open' status, to_date('30/09/2014 03:48:10 PM', 'dd/mm/yyyy hh:mi:ss AM') start_time FROM dual UNION ALL
                    SELECT 'Open' status, to_date('1/10/2014 10:41:57 AM', 'dd/mm/yyyy hh:mi:ss AM') start_time FROM dual UNION ALL
                    SELECT 'Open' status, to_date('4/03/2015 09:59:04 AM', 'dd/mm/yyyy hh:mi:ss AM') start_time FROM dual UNION ALL
                    SELECT 'Queued' status, to_date('18/06/2015 01:31:30 PM', 'dd/mm/yyyy hh:mi:ss AM') start_time FROM dual UNION ALL
                    SELECT 'Open' status, to_date('20/06/2015 10:10:47 PM', 'dd/mm/yyyy hh:mi:ss AM') start_time FROM dual UNION ALL
                    SELECT 'Open' status, to_date('20/06/2015 11:20:11 PM', 'dd/mm/yyyy hh:mi:ss AM') start_time FROM dual UNION ALL
                    SELECT 'Open' status, to_date('27/06/2015 01:18:50 PM', 'dd/mm/yyyy hh:mi:ss AM') start_time FROM dual UNION ALL
                    SELECT 'Completed' status, to_date('27/06/2015 01:22:08 PM', 'dd/mm/yyyy hh:mi:ss AM') start_time FROM dual UNION ALL
                    SELECT 'Completed' status, to_date('28/09/2015 09:31:55 AM', 'dd/mm/yyyy hh:mi:ss AM') start_time FROM dual UNION ALL
                    SELECT 'Completed' status, to_date('5/10/2015 11:57:38 AM', 'dd/mm/yyyy hh:mi:ss AM') start_time FROM dual UNION ALL
                    SELECT 'Closed' status, to_date('11/01/2016 09:31:26 AM', 'dd/mm/yyyy hh:mi:ss AM') start_time FROM dual)
SELECT status,
       MIN(start_time) start_time
FROM   (SELECT status,
               start_time,
               row_number() OVER (ORDER BY start_time, status) - row_number() OVER (PARTITION BY status ORDER BY start_time, status) grp
        FROM   your_table)
GROUP BY status, grp
ORDER BY start_time, status;

STATUS    START_TIME
--------- -------------------
New       30/09/2014 15:48:10
Open      30/09/2014 15:48:10
Queued    18/06/2015 13:31:30
Open      20/06/2015 22:10:47
Completed 27/06/2015 13:22:08
Closed    11/01/2016 09:31:26

N.B. Since you have rows with different statuses having the same start_time, I have added status into the order by, in order to get the results you were after. I don't know if that was a typo, or whether multiple rows really can have the same date.

Also, I assume that the data in your example refers to one "thing", but in your real table, you can have multiple "things" each with their own set of statuses etc.

In that case, you would need to add the column(s) that differentiate the "things" (e.g. id or event_name or etc) into both row_number() analytic functions. (e.g. row_number() over (partition by <thing column(s)> order by start_time, status))

fuzzy join with multiple conditions, Column B is the crushing rate at each timestamp. DataFrame(data=A) df_B = pd.DataFrame(data=B) df_com = pd.concat([df_A,df_B],axis=1).drop('index',axis=​1) groupby and add index columns: material actually crushing """ if df['load'].​sum() == 0: return ans = pd. Cancel and add another image. You can use the Object Explorer to specify a default value for a table column. Object Explorer. In Object Explorer, right-click the table with columns for which you want to change the scale and click Design. Select the column for which you want to specify a default value.

You can also try the SQL for Pattern Matching

WITH tickets(STATUS, START_TIME) AS (
    SELECT 'New', TO_DATE('30/09/2014 3:48:10 PM', 'dd/mm/yyyy hh:mi:ss AM') FROM dual UNION ALL
    SELECT 'Open', TO_DATE('30/09/2014 3:48:10 PM', 'dd/mm/yyyy hh:mi:ss AM') FROM dual UNION ALL
    SELECT 'Open', TO_DATE('1/10/2014 10:41:57 AM', 'dd/mm/yyyy hh:mi:ss AM') FROM dual UNION ALL
    SELECT 'Open', TO_DATE('4/03/2015 9:59:04 AM', 'dd/mm/yyyy hh:mi:ss AM') FROM dual UNION ALL
    SELECT 'Queued', TO_DATE('18/06/2015 1:31:30 PM', 'dd/mm/yyyy hh:mi:ss AM') FROM dual UNION ALL
    SELECT 'Open', TO_DATE('20/06/2015 10:10:47 PM', 'dd/mm/yyyy hh:mi:ss AM') FROM dual UNION ALL
    SELECT 'Open', TO_DATE('20/06/2015 11:20:11 PM', 'dd/mm/yyyy hh:mi:ss AM') FROM dual UNION ALL
    SELECT 'Open', TO_DATE('27/06/2015 1:18:50 PM', 'dd/mm/yyyy hh:mi:ss AM') FROM dual UNION ALL
    SELECT 'Completed', TO_DATE('27/06/2015 1:22:08 PM', 'dd/mm/yyyy hh:mi:ss AM') FROM dual UNION ALL
    SELECT 'Completed', TO_DATE('28/09/2015 9:31:55 AM', 'dd/mm/yyyy hh:mi:ss AM') FROM dual UNION ALL
    SELECT 'Completed', TO_DATE('5/10/2015 11:57:38 AM', 'dd/mm/yyyy hh:mi:ss AM') FROM dual UNION ALL
    SELECT 'Closed', TO_DATE('11/01/2016 9:31:26 AM', 'dd/mm/yyyy hh:mi:ss AM') FROM dual)
SELECT STATUS, START_TIME
FROM tickets
    MATCH_RECOGNIZE (
        ORDER BY START_TIME
        MEASURES
            START_TIME AS START_TIME,
            STATUS as STATUS
        PATTERN ( CHNG )
        DEFINE
            CHNG AS CHNG.STATUS <> PREV(CHNG.STATUS) OR PREV(CHNG.STATUS) IS NULL
    )


STATUS     START_TIME
========== ====================
New        30.09.2014 15:48:10
Open       30.09.2014 15:48:10
Queued     18.06.2015 13:31:30
Open       20.06.2015 22:10:47
Completed  27.06.2015 13:22:08
Closed     11.01.2016 09:31:26

CHNG.STATUS <> PREV(CHNG.STATUS) matches each row where STATUS is different to previous row. PREV(CHNG.STATUS) IS NULL is used to get also the very first row.

[BUG] ReadCSV for Timestamp columns returns an unexpected , Describe the bug A CSV file with columns of type = timestamp does not return [​BUG] ReadCSV for Timestamp columns returns an unexpected value #2657 timestamp that can be used to convert it back to any other timestamp mike-​wendt added this to Needs prioritizing in Bug Squashing on Feb 13. Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

use row_number window function

    select STATUS ,START_TIME from 
    (
    select STATUS,START_TIME,
    row_number() over (partition by STATUS,EXTRACT(YEAR FROM START_TIME) order by START_TIME) rn
   from events_tab
    ) t where rn=1

[BUG] Timestamp casting to Datetime from `us` to `ns` results in , Casting a timestamp of us to datetime ns returns wrong value github-actions bot added this to Needs prioritizing in Bug Squashing on Apr 2 But would like to keep the discussion open for other solutions. match the timestamp units to the format and then cast the results to the desired timestamp column. I have table field type TIMESTAMP(6) and wanted to get a new timestamp value after adding 10 seconds, but result was giving different timestamp format (append "000" in nanoseconds value), so I have to cast the result into timestamp(6) CREATE TABLE MyTable (CREATEDATETIME TIMESTAMP(6)) / INSERT INTO MyTable VALUES (SYSTIMESTAMP) /

Use LAG Function as you need to track the change in status:

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=38a991b698c858f6f0417c7d4c0dc9d3

with cte1 (st,dt) as 
(
select 'New' as st, '30/09/2014 3:48:10 PM' as dt from dual
union all
select 'Open' as st, '30/09/2014 3:48:10 PM' as dt from dual
union all
select 'Open' as st, '20/09/2014 3:48:10 PM' as dt from dual
union all
select 'Qued' as st, '18/06/2015 1:31:30' as dt from dual

)
select st, min(case when st<>prev_order_date then dt else dt end) as d
from
(
SELECT st, dt,
LAG (st,1) OVER (ORDER BY st) AS prev_order_date
FROM cte1
)a
group by st

microsoft excel, Column C = numbers Column I = Datevalue timestamp (2/17/20 15:10 for example). I figured out the max value between the timestamp  One TIMESTAMP column in a table can have the current timestamp as the default value for initializing the column, as the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.

How to Remove Duplicates in Google Sheets in Five Different Ways, In other words, it compares each row of data and removes any rows that First, create a new column next to the data column you want to check for This new column consists of all of the “unique” columns squashed Would anyone know how the script could be modified to ignore the Timestamp column? From now on, when changing data in column C, the timestamp will be populated into corresponding cells in column E as below screenshot shown. Tip. Date Picker. The Insert Date utility of Kutools for Excel helps you to quickly insert date with specified date format into selected cell.

Fifth European Workshop on Structural Health Monitoring 2010, During settlement and, more often, during an earthquake, the columns of a building including concrete cracks, cover spalling, crushing and reinforcement yield. the strain data, with timestamp and sensor identification tag, are stored in a  For date, time, and timestamp columns, the default value is the current date, time, or timestamp. When inserting a block of records, the default date/time value is extracted from the system when the block is written. This means that the column will be assigned the same default value for each row in the block.

[PDF] data.table, of column names, list, data.frame or data.table. integer and sees column names as if they are variables) and can evaluate to any of the other types. POSIXct UTC is squashed to 17 digits (including 3 digits of creation_time_utc - Current timestamp in UTC time just before the header is written. • schema  Using default values on database columns helps to insulate database design issues from application code. Find out how to change a column's default value at a later date using a single command.

Comments
  • Thanks for the answer. I'm a bit confused, where does event_tab (the table name) go in this?
  • Thanks I think this work. I'll test with some other data and accept your answer in a little while.
  • Unfortunately this actually gives the wrong result. Instead of Open 20/06/2015, it gives me Open 4/03/2015.
  • Thanks for the answer. Wouldn't that select two records from the first Open group? There are records for 2014 and 2015 in there.
  • yes, but it will select min start time for 2014 by status, and 2015 by status, only one confusion from your data point is Open 4/03/2015 9:59:04 AM - why you exclude this from your expected output? - is there any logic
  • Yes, these are events that happened in each state. I want to make a timeline of state changes from it.
  • modified the answer with demo , you can check now @Krumia
  • Unfortunately this hardcodes the values. I want a dynamic way to filter the very first row of each group.