Selecting last instance of a record.

snoop_uk-airsoft · #1 (**permalink**) 09-05-06, 17:38

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
Table 1
r_id,r_c_id,r_e_date

Table 2
ra_id,rec_id
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.

so for example

wanting to simply retrieve
Table 1 table 2
r_id r_c_id r_e_date ra_id Rec_id ra_e_date
10 1 01/09/2006 10 03 01/09/2006
09 2 01/09/2006 09 02 01/09/2006
08 2 01/08/2006 08 02 01/08/2006
07 1 01/08/2006 <<<<<<<<<<This record here. 07 01 01/08/2006

All the appropriate records will be limited to the same r_c_id as it's a foreign key on another table.

As you can see a change was committed which changed the rec_id on table two, this is indiciative of a material change. I am needing to find those cases.

is there a simple command in sql/informix to retrieve the second to last record/distinct record without having to specify a r_c_id?

Tyveleyn · #2 (**permalink**) 09-05-06, 17:49

Quote:

I'm wanting to find instance which don't match up between table 1 and table 2 on r_e_date/ra_e_date however limited to looking at the last instance of this under table 1 - 1 record.

I don't know what exactly you mean by that. Can you post the query you've used, perhaps that makes things clear to me?

Grts

snoop_uk-airsoft · #3 (**permalink**) 09-06-06, 03:00

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.

Tyveleyn · #4 (**permalink**) 09-06-06, 05:33

So if I'm correct your tables look like this?

Code:

Table 1 			table 2
r_id r_c_id 	r_e_date 	ra_id Rec_id 	ra_e_date
  10 	  1   01/09/2006 	   10 	  03   01/09/2006 <Record 1
  09 	  2   01/09/2006 	   09 	  02   01/09/2006
  08 	  2   01/08/2006 	   08 	  02   01/08/2006 
  07 	  1   01/08/2006	   07 	  01   01/08/2006 <Record 2

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:

Code:

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...)

Regards

snoop_uk-airsoft · #5 (**permalink**) 09-06-06, 10:37

tab1.r_id will be matched with a copy of tab2.r_id however tab1.r_c_id is different to tab2.rec_id

However I suspect the script you just provided MIGHT well be what I'm looking for. Will try and let you know.

Internet Services

Web Development

Digital Marketing

Consumer Tech