When DISTINCT does not work
If you have experience with writing SQL codes, but SQL is not your primary job, you might get a feeling that you are almost there as an expert, but sometimes, you just cannot seem to reach a very straightforward goal.
Recently a business process analyst came to me for some SQL tips.
Here is the challenge he is facing.
When I query T_HISTORY I get multiple records for a Loan Number. How do I return only one or the last record? I tried adding Distinct to the select but that didn’t work.
This is where DISTINCT will not work. We will have to use sub-query, or temp table.
I showed him one way to do this.
The key is in the second part to group the data by the loan number, then get the max ID. Temp table #EXPORT2 should only have the unique service loan number after this.