Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2010
    Posts
    15

    Unanswered: First occurrence

    I have a table, called tblActions.

    Code:
    actionid id actionname timestamp
    1 1 ARRIVED 2011-01-25-10.28.37.000000
    2 1 REGISTERED 2011-01-25-10.32.42.000000
    3 2 SCHEDULED 2011-01-25-10.59.27.000000
    4 2 ARRIVED 2011-01-25-11.05.11.000000
    5 1 COMPLETE 2011-01-25-11.17.22.000000
    6 2 REGISTERED 2011-01-25-11.20.11.000000
    7 3 ARRIVED 2011-01-25-11.28.37.000000
    8 2 COMPLETE 2011-01-25-11.31.31.000000
    9 3 SCHEDULED 2011-01-25-11.39.54.000000
    10 3 COMPLETE 2011-01-25-11.51.37.000000
    Three people go through the process. I need to find the time the first action happened. So my result would look like this:

    Code:
    1 1 ARRIVED 2011-01-25-10.28.37.000000
    3 2 SCHEDULED 2011-01-25-10.59.27.000000
    6 3 ARRIVED 2011-01-25-11.28.37.000000
    The first step is not always "ARRIVED" or "SCHEDULED" there are other possibilities. So I need to get the earliest time. I also only want to select people who have been "complete"

    My thought was this, but it isn't working

    Code:
    select actionid, id, actionname, min(timestamp) 
    from tblActions 
    where id in 
    (select id from tblActions where actionname='COMPLETE') 
    group by id, actionid, actionname, timestamp 
    order by timestamp
    But this finds everything. Also, I don't think I can select the first row, it has to be done by time. There's places where the times aren't in order.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You want something like:

    Code:
    with t1 (id, min_time) as
    (select id,min(timestamp)
    from tblactions 
    where id in (select id from tblactions where actionname = 'COMPLETE'))
    select a.actionid,a.id,a.action_name,x.min_time
    from t1 as x
    inner join tblactions as a on (x.id = a.id and x.min_time = a.timestamp)
    Andy

  3. #3
    Join Date
    Jun 2010
    Posts
    15
    Quote Originally Posted by ARWinner View Post
    You want something like:

    Code:
    with t1 (id, min_time) as
    (select id,min(timestamp)
    from tblactions 
    where id in (select id from tblactions where actionname = 'COMPLETE'))
    select a.actionid,a.id,a.action_name,x.min_time
    from t1 as x
    inner join tblactions as a on (x.id = a.id and x.min_time = a.timestamp)
    Andy
    Awesome, thanks.

    Out of curiosity, is there any way to "normalize" the actionname field? When I get the results, I obviously see the different names, ARRIVED, SCHEDULED, etc in the actionname column. Is there any way (without modifying the data in the database) to put a static value in there? Even though the actual data in the database says SCHEDULED, I want the query to return ARRIVED for that column every time.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Instead of returning a column, return a literal.

    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •