sql query
Hi, I am using ESQL/C (Informix) and trying
to formulate a query that would do the following:
There is table A that has multiple records.
The fetch key for table A is value. There
can at most 2 rows with the same record_id
Table A
has 4 fields: record_id, status (previous or current)
name, value.
I want to get a sorted list of records by record_id
and in the case where 2 rows exist with the same
record_id, I want to get the one with status set
to current.
How do I setup a query to do this??
select * from tableA where value=<passed in> ORDER by record_id ???
Should I do two selects?
Thanks for any help! please email lora@ragingbull.com
Sent via Deja.com http://www.deja.com/
Before you buy.
Hi Lora,
lora@ragingbull.com schrieb:
> Hi, I am using ESQL/C (Informix) and trying
> to formulate a query that would do the following:
>
> There is table A that has multiple records.
> The fetch key for table A is value. There
> can at most 2 rows with the same record_id
>
> Table A
> has 4 fields: record_id, status (previous or current)
> name, value.
>
> I want to get a sorted list of records by record_id
> and in the case where 2 rows exist with the same
> record_id, I want to get the one with status set
> to current.
>
> How do I setup a query to do this??
>
> select * from tableA where value=<passed in> ORDER by record_id ???>
Perhaps do something like this :
$select * from tableA where value=<passed in> into temp t_tableA;
$select record_id from t_tempA group by record_id having count(*) > 1
into temp t_rid;
$delete from t_tableA where status!=current and record_id in select
record_id from t_rid;
$select * from t_tableA ORDER by record_id;
$drop table t_rid;
$drop table t_tableA;
HTH
Dirk
>
> Should I do two selects?
> Thanks for any help! please email lora@ragingbull.com
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
lora@ragingbull.com wrote:
>
> Hi, I am using ESQL/C (Informix) and trying
> to formulate a query that would do the following:
>
> There is table A that has multiple records.
> The fetch key for table A is value. There
> can at most 2 rows with the same record_id
So the schema for TableA is:
record_id
status
name
value
> Table A
> has 4 fields: record_id, status (previous or current)
> name, value.
>
> I want to get a sorted list of records by record_id
> and in the case where 2 rows exist with the same
> record_id, I want to get the one with status set
> to current.
If there is only one record will it always have status == 'current' or
can its status be 'previous'?
> How do I setup a query to do this??
Assuming that if there is only one row it is status 'current' then:
SELECT *
FROM TableA
WHERE status = 'current'
ORDER BY record_id;
But that's too easy. Let's assume that the status of a singleton record_id
could be 'previous' or 'current':
SELECT *
FROM TableA -- Select all record_id's with a status current row
WHERE status = 'current'
UNION
SELECT *
FROM TableA -- Select all record_id's WITHOUT a status current row
WHERE status = 'previous'
AND record_id NOT IN (
SELECT record_id
FROM TableA -- by filtering out those selected in the first part
WHERE status = 'current'
)
ORDER BY record_id;
> select * from tableA where value=<passed in> ORDER by record_id ???>
> Should I do two selects?
Sort of, two selects with a UNION in the latter case.
Art S. Kagel
First, I'm new to Informix and know that using a sub query would be a better
way to solve your problem but am unsure how to do that as of yet. That said,
here is a (albeit not the best) solution to your problem...
I simulated your data condition by executing the following SQL statements...
create table tablea (id integer not null, status char(20) not null,
name char(20) not null, val char(20) not null);
insert into tablea values( 1, "past", "age", "28");
insert into tablea values( 1, "current", "age", "29");
insert into tablea values( 2, "current", "age", "35");
insert into tablea values( 3, "past", "age", "62");
insert into tablea values( 4, "current", "age", "55");
insert into tablea values( 5, "past", "age", "33");
insert into tablea values( 5, "current", "age", "34");
Next, I executed the following set of SQL statements to demonstrate one
solution to your situation. Note that I allowed one record to have a "past"
but not a "current" incase you have that scenario. Note also that you would
need to drop the t_tablea before each run.
create table t_tablea (id integer not null, count integer not null);
INSERT INTO t_tablea ( id, count )
SELECT tablea.id, Count(tablea.id) AS count
FROM tablea
GROUP BY tablea.id;
SELECT tablea.id, tablea.status, tablea.name, tablea.val
FROM t_tablea, tablea
WHERE (( tablea.id = t_tablea.id AND tablea.status="current"
AND t_tablea.count>1 )
OR
(tablea.id=t_tablea.id AND t_tablea.count=1 ))
ORDER BY tablea.id;
Finally, this lead to the following output...
id status name val
1 current age 29
2 current age 35
3 past age 62
4 current age 55
5 current age 34
<lora@ragingbull.com> wrote in message news:8essh9$h3$1@nnrp1.deja.com...
> Hi, I am using ESQL/C (Informix) and trying
> to formulate a query that would do the following:
>
> There is table A that has multiple records.
> The fetch key for table A is value. There
> can at most 2 rows with the same record_id
>
> Table A
> has 4 fields: record_id, status (previous or current)
> name, value.
>
> I want to get a sorted list of records by record_id
> and in the case where 2 rows exist with the same
> record_id, I want to get the one with status set
> to current.
>
> How do I setup a query to do this??
>
> select * from tableA where value=<passed in> ORDER by record_id ???>
> Should I do two selects?
> Thanks for any help! please email lora@ragingbull.com
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.