How to retrieve DISTINCT values from this table?

Question

Here goes my table values. ( 7 Records )

SELECT * FROM tbl1

I can't post the image as my rep is low. So i am linking it here http://i.stack.imgur.com/CFl0u.png

I wrote a query to avoid the last record, but i am still getting the last record. (ALL I NEED IS DISTINCT EMAILS)

SELECT DISTINCT CandEmail,CandName,EmployerId,ContNum,IsDeleted,CandPortalId FROM tbl1
WHERE EmployerId = 7 AND IsDeleted = 0

The above query still retrieves the same 7 records with last duplicate email record.

Answer 1

You can use ROW_NUMBER with OVER clause:

WITH CTE AS
(
  SELECT CandEmail,CandName,EmployerId,ContNum,IsDeleted,CandPortalId
        , RN = ROW_NUMBER() OVER (PARTITION BY CandEmail ORDER BY ContNum DESC)
  FROM tbl1
  WHERE IsDeleted = 0
)
SELECT CandEmail,CandName,EmployerId,ContNum,IsDeleted,CandPortalId
FROM CTE WHERE RN = 1

OVER Clause (Transact-SQL)

Determines the partitioning and ordering of a rowset before the associated window function is applied. That is, the OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window.