Hello
I have a transaction table with 300 million rows and the grain is ID,Version
One ID can have multiple versions.
| ID | NAME | VERSION | START_DATE | END_DATE |
| 1 | A | 1 | 2/1/2017 | 2/10/2017 |
| 1 | A | 2 | 2/1/2017 | 2/10/2017 |
| 1 | A | 3 | 2/1/2017 | 2/10/2017 |
| 2 | B | 1 | 2/4/2017 | 2/14/2017 |
| 2 | NULL | 2 | 2/4/2017 | 2/14/2017 |
| 2 | B | 3 | 2/4/2017 | 2/14/2017 |
I am trying to retrieve unique rows from the transaction table based on the ID & max(version) of the row.
My output looks like
| ID | NAME | VERSION | START_DATE | END_DATE |
| 1 | A | 3 | 2/1/2017 | 2/10/2017 |
| 2 | B | 3 | 2/4/2017 | 2/14/2017 |
Given I need to query a 300 million row table, which can grow fast, Can you suggest any approaches to retrieve unique rows ?
Thank you again