Home > SQL Challenge > When DISTINCT does not work

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.

image

Categories: SQL Challenge Tags: , , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: