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.