How can I extract just the last(i.e. most current) entry from a set of records based on date
Consider the following: There are multiple records in a table that contain the following fields/values: userid phone orderdate orderstatus a1234567 111-222-3333 2001-05-18 Completed a1234567 111-222-3333 2002-02-02 Completed a1234567 111-222-3333 2003-03-05 Pending Completion Using PL/SQL I'd like to get just the record with the most recent orderdate value and then return values for the orderstatus and orderdate into 2 variables: LET c_orderstatus, c_orderdate = (SELECT orderstatus, max(orderdate) FROM c WHERE userid = c_userid AND phone = a_phone AND orderstatus in ('Completed', 'Pending Completion', 'Assigned', 'New') GROUP BY orderstatus); The problem with this is that the query will return 2 rows which is not what I want since the orderstatus values are unique on max(orderdate). I do have to check for the status values as indicated in the query since there are a number of other orderstatus values that are to be ignored. I tried to break this up into 2 separate queries as well, but still can't get the results I need. Can someone help me out here? Thanks. Jeff
JeffC wrote:
> Consider the following:
>
> There are multiple records in a table that contain the following
> fields/values:
>
> userid phone orderdate orderstatus
> a1234567 111-222-3333 2001-05-18 Completed
> a1234567 111-222-3333 2002-02-02 Completed
> a1234567 111-222-3333 2003-03-05 Pending Completion
>
> Using PL/SQL I'd like to get just the record with the most recent
> orderdate value and then return values for the orderstatus and
> orderdate into 2 variables:
>
> LET c_orderstatus, c_orderdate = (SELECT orderstatus, max(orderdate)
> FROM c WHERE userid = c_userid AND phone = a_phone AND orderstatus in
> ('Completed', 'Pending Completion', 'Assigned', 'New') GROUP BY
> orderstatus);
>
> The problem with this is that the query will return 2 rows which is
> not what I want since the orderstatus values are unique on
> max(orderdate). I do have to check for the status values as indicated
> in the query since there are a number of other orderstatus values that
> are to be ignored.
>
> I tried to break this up into 2 separate queries as well, but still
> can't get the
> results I need.
>
> Can someone help me out here?
>
> Thanks.
> Jeff
SELECT orderstatus, orderdate
FROM c t1
WHERE t1.userid = c_userid ANDt1.phone = a_phone AND
t1.orderstatus in ('Completed', 'Pending Completion', 'Assigned', 'New')
and t1.orderdate =
(select max(orderdate) from c t2 where t2.userid = c_userid AND t2.phone
= a_phone AND t2.orderstatus in ('Completed', 'Pending Completion',
'Assigned', 'New'))
Based on a simple test like :
create table tbp (col1 int, col2 int);
insert into tbp values (1,1);
insert into tbp values (1,2);
insert into tbp values (1,3);
insert into tbp values (1,4);
insert into tbp values (2,1);
insert into tbp values (2,2);
insert into tbp values (2,3);
insert into tbp values (2,4);
select col1, col2 from tbp m1
where col1 = 2 and
col2 = (select max(col2) from tbp s1 where s1.col1 = m1.col1)
JeffC wrote:
>Consider the following:
>
>There are multiple records in a table that contain the following
>fields/values:
>
>userid phone orderdate orderstatus
>a1234567 111-222-3333 2001-05-18 Completed
>a1234567 111-222-3333 2002-02-02 Completed
>a1234567 111-222-3333 2003-03-05 Pending Completion
>
>Using PL/SQL I'd like to get just the record with the most recent
>
There is no PL/SQL in Informix.
>orderdate value and then return values for the orderstatus and
>orderdate into 2 variables:
>
>LET c_orderstatus, c_orderdate = (SELECT orderstatus, max(orderdate)
>FROM c WHERE userid = c_userid AND phone = a_phone AND orderstatus in
>('Completed', 'Pending Completion', 'Assigned', 'New') GROUP BY
>orderstatus);
>
>
select orderstatus,orderdate from c
where userid = c_userid and phone = a_phone and orderstatus in
('Completed', 'Pending Completion', 'Assigned', 'New')
and orderdate = (select max(orderdate) from c x
where c.userid = x.userid and x.phone = c.phone);
I think this is right. It is a correlated query using the same table.
Should work in any SQL (dbaccess, SPL etc.)
HTH
Michael
>The problem with this is that the query will return 2 rows which is
>not what I want since the orderstatus values are unique on
>max(orderdate). I do have to check for the status values as indicated
>in the query since there are a number of other orderstatus values that
>are to be ignored.
>
>I tried to break this up into 2 separate queries as well, but still
>can't get the
>results I need.
>
>Can someone help me out here?
>
>Thanks.
>Jeff
>
>
Jeff,
I would have you try this but 'FIRST 1' is not allows in assignment...
LET c_orderstatus, c_orderdate = (SELECT FIRST 1 orderstatus, max(orderdate)
FROM c WHERE userid = c_userid AND phone = a_phone AND orderstatus in
('Completed', 'Pending Completion', 'Assigned', 'New') GROUP BY
orderstatus);
So instead try this...
FOREACH
SELECT FIRST 1 orderstatus, max(orderdate) INTO c_orderstatus, c_orderdate
FROM c WHERE userid = c_userid AND phone = a_phone AND orderstatus in
('Completed', 'Pending Completion', 'Assigned', 'New') GROUP BYorderstatus
END FOREACH;
This will return at most 1 row. You may need to add an order by if certain
order status has priority when more than one row could be returned without
FIRST 1. You could also leave off the FIRST 1 as follows...
FOREACH
SELECT orderstatus, max(orderdate) INTO c_orderstatus, c_orderdate
FROM c WHERE userid = c_userid AND phone = a_phone AND orderstatus in
('Completed', 'Pending Completion', 'Assigned', 'New') GROUP BYorderstatus
EXIT FOREACH;
END FOREACH;
Gregg Walker
"JeffC" <gninnacj@netscape.net> wrote in message
news:c703eed2.0306210808.437bc13f@posting.google.com...
> Consider the following:
>
> There are multiple records in a table that contain the following
> fields/values:
>
> userid phone orderdate orderstatus
> a1234567 111-222-3333 2001-05-18 Completed
> a1234567 111-222-3333 2002-02-02 Completed
> a1234567 111-222-3333 2003-03-05 Pending Completion
>
> Using PL/SQL I'd like to get just the record with the most recent
> orderdate value and then return values for the orderstatus and
> orderdate into 2 variables:
>
> LET c_orderstatus, c_orderdate = (SELECT orderstatus, max(orderdate)
> FROM c WHERE userid = c_userid AND phone = a_phone AND orderstatus in
> ('Completed', 'Pending Completion', 'Assigned', 'New') GROUP BY
> orderstatus);
>
> The problem with this is that the query will return 2 rows which is
> not what I want since the orderstatus values are unique on
> max(orderdate). I do have to check for the status values as indicated
> in the query since there are a number of other orderstatus values that
> are to be ignored.
>
> I tried to break this up into 2 separate queries as well, but still
> can't get the
> results I need.
>
> Can someone help me out here?
>
> Thanks.
> Jeff