Joining records in a table

[DexterWard]

I have an audit table with before and after records of changes made to a user table. So every time an update is made to the user table, a record is logged to the audit table with the current value for each field, and and a second record is logged with the new value for each field. So if a record was disabled and that was the only change made, the 2 records would look like this:

Mod_type	user ID	Email	change #	Active
OLD	123	[email protected]	152	No
NEW	123	[email protected]	152	Yes

I need to match the 2 records by user ID and change # so I can find all the records where specific changes were made, such as going from inactive to active, or where the email address changed, etc. I've looked into selfjoin, appendpipe, etc., but none of them seem to be what I need. I'm trying to say "give me all the records where the active field was changed from "No" to "Yes" and the Mod_Type is "New"". 

Thanks for any help.

[yuanliu]

Your "table" must come with a sequence or the whole problem is unsolvable.  The sequence may come in the form of a _time field, or a special field such as sequence_number, or in the form of sheer order of the table.

The whole point is, you can use transaction command to get what you need if your table

1. Has a _time field and
2. Is in reverse time order.

| transaction "change #" "user ID" startswith="Mod_type=OLD" endswith="Mod_type=NEW"

If, for any reason, your "table" doesn't come with a _time field, you can always make sure it is in reverse time order, and make up a _time field.  You can also use stats to do the same.  The bottom line is: join is seldom the answer.

Here is a data emulation for you to play with and compare with real data.

| makeresults format=csv data="Mod_type, user ID,Email,change #,Active
NEW,123,[email protected],152,Yes
OLD,123,[email protected],152,No"
| eval _time = now()