T-SQL to return latest customer email
Posted on 2008-11-04
I have a two table database:
I want to return a dataset of all customers and just their latest active email (they can have more than one).
In the Email table there are 5 fields:
If EmailStatus is 1 (active) I want to order all their associated addresses by DateEntered DESC and grab the top 1. Thus I only return their "latest" email address.
I assume this is needing some kind of subquery.
I'd like the query to simply return (one for each active customer):
Since they can technically have more than one email address in our system, I am getting back 3 rows for the same customer (if they happen to have 3 active email addresses in our system).
How can I query the email table as part of my overall query to return just 1 row per customer?