Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2006
    Posts
    4

    Unanswered: Selecting last instance of a record.

    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?
    Last edited by snoop_uk-airsoft; 09-06-06 at 04:08.

  2. #2
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    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

  3. #3
    Join Date
    Aug 2006
    Posts
    4
    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.

  4. #4
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    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
    Last edited by Tyveleyn; 09-06-06 at 10:41.

  5. #5
    Join Date
    Aug 2006
    Posts
    4
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •