SQL Problem
Given a table with 3 columns (A int not null, B int not null, and VALIDFROM date) with A being the primary key and an additional unique constraint on (B, VALIDFROM) and the fact that there might be several records with the same value for B, but all with a different VALIDFROM (0 or 1 record of which with VALIDFROM set to NULL), here's what I want to do: select the most recent record (greatest VALIDFROM) for each group of records with the same value for B. A NULL value for VALIDFROM (if there is one for that value of B) should be considered as earlier than (less than) any other record for that value of B (if there are any). Example: A B VALIDFROM = = ========= 1 1 2 1 12/31/93 3 1 12/31/94 4 2 12/31/90 5 3 6 4 12/31/90 7 4 12/31/91 In this case the query should return 3 1 12/31/94 4 2 12/31/90 5 3 7 4 12/31/91 Is there any way to handle this query in one single select statement (ideally without any subselects) in Informix SQL? I've tried all sorts of things including GROUP BY and HAVING as well as the newly supported NVL function but failed so far. It seems to me that this should be a rather common problem with historically maintained data except perhaps for the earliest date being NULL instead of a default value such as 01/01/1600 (which I won't be able to do anything about, by the way), so maybe I'm lucky and someone has found a solution for it. My environment is Informix Dynamic Server Version 7.30.UC5 running on Sun Sparc Solaris 2.6. Any help would be greatly appreciated. Cheers E
Should...
select A, B, max(VALIDFROM)
from table
group by 1,2
order by 2,3;
... satisfy ?
EA <appshome@geocities.com> a 'crit dans le message :
7vnb9s$8tk$1@pollux.ip-plus.net...
Given a table with 3 columns (A int not null, B int not null, and VALIDFROM
date) with A being the primary key and an additional unique constraint on
(B, VALIDFROM) and the fact that there might be several records with the
same value for B, but all with a different VALIDFROM (0 or 1 record of which
with VALIDFROM set to NULL), here's what I want to do:
select the most recent record (greatest VALIDFROM) for each group of records
with the same value for B.
A NULL value for VALIDFROM (if there is one for that value of B) should be
considered as earlier than (less than) any other record for that value of B
(if there are any).
Example:
A B VALIDFROM
= = =========
1 1
2 1 12/31/93
3 1 12/31/94
4 2 12/31/90
5 3
6 4 12/31/90
7 4 12/31/91
In this case the query should return
3 1 12/31/94
4 2 12/31/90
5 3
7 4 12/31/91
Is there any way to handle this query in one single select statement
(ideally without any subselects) in Informix SQL? I've tried all sorts of
things including GROUP BY and HAVING as well as the newly supported NVL
function but failed so far.
It seems to me that this should be a rather common problem with historically
maintained data except perhaps for the earliest date being NULL instead of a
default value such as 01/01/1600 (which I won't be able to do anything
about, by the way), so maybe I'm lucky and someone has found a solution for
it.
My environment is Informix Dynamic Server Version 7.30.UC5 running on Sun
Sparc Solaris 2.6.
Any help would be greatly appreciated.
Cheers
E
EA wrote:
>
> Given a table with 3 columns (A int not null, B int not null, and VALIDFROM
> date) with A being the primary key and an additional unique constraint on
> (B, VALIDFROM) and the fact that there might be several records with the
> same value for B, but all with a different VALIDFROM (0 or 1 record of which
> with VALIDFROM set to NULL), here's what I want to do:
>
> select the most recent record (greatest VALIDFROM) for each group of records
> with the same value for B.
> A NULL value for VALIDFROM (if there is one for that value of B) should be
> considered as earlier than (less than) any other record for that value of B
> (if there are any).
>
> Example:
>
> A B VALIDFROM
> = = =========
> 1 1
> 2 1 12/31/93
> 3 1 12/31/94
> 4 2 12/31/90
> 5 3
> 6 4 12/31/90
> 7 4 12/31/91
>
> In this case the query should return
>
> 3 1 12/31/94
> 4 2 12/31/90
> 5 3
> 7 4 12/31/91
>
> Is there any way to handle this query in one single select statement
> (ideally without any subselects) in Informix SQL? I've tried all sorts of
> things including GROUP BY and HAVING as well as the newly supported NVL
> function but failed so far.
>
> It seems to me that this should be a rather common problem with historically
> maintained data except perhaps for the earliest date being NULL instead of a
> default value such as 01/01/1600 (which I won't be able to do anything
> about, by the way), so maybe I'm lucky and someone has found a solution for
> it.
You cannot do this in a single query without a subquery. I will suggest
that you will have to a subquery or use several queries which add to a
temp table and then select the results from there or by joining to the
temp table. One approach then is:
select B, MAX(validfrom) from atable GROUP BY 1
into temp fred;
select atable.*
from atable, fred
where fred.b = atable.b and fred.validdate = atable.validdate;
drop table fred;
Art S. Kagel