I've got two transaction tables in Netezza set up like the below. When joining the tables on ID and transactionCount, and trying to return Answer, there will be nulls for row 9998, 9996, 9995 etc. How would I return all transactionCount values with the Answer column populated with the last result?
So for example 9998 would return U, 9996 onward would return Y, 9988 would return N, and so on.
ID transactionCount ID transactionCount Answer
1 9999 1 9999 U
1 9998 1
1 9997 1 9997 Y
1 9996 1
2 9999 2 9999 Y
2 9998 2
2 9997 2
2 9996 2 9996 N
2 9995 2
3 9999 3 9999 Y
3 9998 3
3 9997 3 9997 N
3 9996 3
3 9995 3
Thanks for any help.
You didn't specify an RDBMS so I'll just use MySQL.
Here is a fiddle to show it working: http://sqlfiddle.com/#!2/dde35e/3
EDIT...
In response to a change to the question, the query becomes:
And the sql fiddle is now: http://sqlfiddle.com/#!2/36aacc/1