Table Information:
I have these columns say ACCOUNT_ID, ACCOUNT_CODE and SEQ_NUM (and few more needless to mention). For every account ID and Code combination there can be multiple entries in the table, with different sequence numbers. The first entry for the account ID and Code combination starts with 98 and for every additional entry the sequence number is decreased. Effectively I'll have sequence numbers ranging from 98 thru 1 (normally i'll not have more than 50 entries for each acct id and code combo) for every new entry of account id and code. This way the entry with the least seq_num is the most recent.
I need to write query that should give me the following result:
If there are more than 10 entries for the account id and code combination, then i need the oldest records after the recent 10 entries. For an example if there are 30 entries for an account (recent seq_num would be 69) id and code combo, i need the records with seq_num greater than 78.
I'll require this query to run on mainframe z/OS and the DB2 version is 8.1. When researching for this I found out the row_number() function, but it doesnt work with v8.1. Is there any other function equivalent to row_number in v8.1 or if someone could provide a solution to this problem it'd be great. Also I'd prefer a query rather than stored procedures or UDF.
I have these columns say ACCOUNT_ID, ACCOUNT_CODE and SEQ_NUM (and few more needless to mention). For every account ID and Code combination there can be multiple entries in the table, with different sequence numbers. The first entry for the account ID and Code combination starts with 98 and for every additional entry the sequence number is decreased. Effectively I'll have sequence numbers ranging from 98 thru 1 (normally i'll not have more than 50 entries for each acct id and code combo) for every new entry of account id and code. This way the entry with the least seq_num is the most recent.
I need to write query that should give me the following result:
If there are more than 10 entries for the account id and code combination, then i need the oldest records after the recent 10 entries. For an example if there are 30 entries for an account (recent seq_num would be 69) id and code combo, i need the records with seq_num greater than 78.
I'll require this query to run on mainframe z/OS and the DB2 version is 8.1. When researching for this I found out the row_number() function, but it doesnt work with v8.1. Is there any other function equivalent to row_number in v8.1 or if someone could provide a solution to this problem it'd be great. Also I'd prefer a query rather than stored procedures or UDF.