Hello, Trying to retrieve a number of records, however constantly come unglued.
the Situation as it stands.
I want to in informix retrieve information from
I'm wanting to find cases in table one where the last record in table two for this r_c_id doesn't match the record before however r_c_id does NOT exist in table two, only table one. I am not at liberty to amend the database structure to add this in.
I'm not sure how to explain it mate.
the query I've used so far has been TOTALLY unsuccessful unless I specify a specific r_c_id which is of no use to me as I need to find all instances of the change.
I've re-stated the table layout as I think I was a little vague.
Basically need to find the LAST instance of r_c_id 1 which is NOT current. as you can imagine though, there might be multiples of r_c_id 1 which stretch back over time, finding against all of them is pointless.
Last edited by snoop_uk-airsoft; 09-06-06 at 04:10.
And tab1.r_id = tab2.ra_id is always true? And tab2.Rec_id is a representation of tab1.r_c_id? Which record would you have returned, 1 or 2?
I"m sorry but I've been reading your remarks over and over again and still don't know what exactly you're looking for. Is it the second newest record in a table given a certain column condition? With this query you can select the (fysically) last (newest) but one record in table 1 with the same r_c_id:
SELECT * FROM table_1 a
WHERE rowid = (SELECT max(rowid)
FROM table_1 b
WHERE b.r_c_id = a.r_c_id
AND rowid < (SELECT max(rowid)
FROM table_1 c
WHERE c.r_c_id = b.r_c_id))
which in case of the Table 1 example above would return r_id: '07' (Record 2, if r_e_date is the date of insertion). And if there's only one row for the r_c_id it returns none.
Rowid is an implicit column that marks the relative position of a row within a table; if no ORDER BY clause is specified the results of a query are ordered by rowid. As it gets an unique successive number with every insert it marks the sequence in which rows were inserted. (But somehow I've got the feeling this is not what you're looking for...)