First occurrence

Pryach · #1 (**permalink**) 01-25-11, 14:39

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.

ARWinner · #2 (**permalink**) 01-25-11, 15:54

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

Pryach · #3 (**permalink**) 01-26-11, 12:12

Quote:

Originally Posted by ARWinner

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.

ARWinner · #4 (**permalink**) 01-26-11, 12:15

Instead of returning a column, return a literal.

Andy

Internet Services

Web Development

Digital Marketing

Consumer Tech