Looks like a recipe to create invalid data.
The easiest way to get each first record with a key value is creating a unique index. In this case
Code:
INDEX ON Item Tag uItem UNIQUE
Which would give you
[pre] Item srp tpr tax date
12345 0.99 N
54321 77 42 N[/pre]
And yes, I know that's not what you want, but a) the only SQL way to combine data based on common Item would be GROUP BY Item but there is no SQL aggregation function giving "first non-empty, non-null value", so that's not a standard way to group data and thus very questionable. And even when you could do that in some way it requires order and since dates are not always given the only possibility I see is the recno, like Mike also already assumed.
I'd say find a better way. If, for example, each record would have net price, tax, gross price, a missing tax value would rather be calculated from the prices than taken from a neighbor record, even though it would make sense if same Item means the same product with the same tax rate, you can get contradicting new tuples of data.
Since I don't know the meaning of the data I can't tell how that's working out in your case, but I'll pass this one, as that looks to me like a case you should rethink, it's nothing I ever saw in cleansing data and I did a lot of data migrations and data cleansing. I'd offer a tool to find such data gaps, list all rows of the same Item and offer manual data recombination. For example, it would be ideal to know how this happens and then being able to reconstruct original data that would result in such data gaps.
Bye, Olaf.
Olaf Doschke Software Engineering