How to get DISTINCT values ​​from this table?

Question

Here are the values ​​of my tables. (7 entries)

SELECT * FROM tbl1 

I can’t post the image because my reputation is low. So I link it here http://i.stack.imgur.com/CFl0u.png

I wrote a request to avoid the last record, but I still get the last record. (EVERYTHING THAT I NEED TO EMAIL)

 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 the last duplicate email record.

Answer 1

If you don’t care what ContNum you get, then you can put an aggregate function in this field, and then GROUP BY rest:

 SELECT CandEmail, CandName, EmployerId, MIN(ContNum) ContNum, -- or you can use MAX() IsDeleted, CandPortalId FROM tbl1 WHERE EmployerId = 7 AND IsDeleted = 0 GROUP BY CandEmail, CandName, EmployerId, IsDeleted, CandPortalId 

Or, as others have pointed out, you should also use aggregate functions for other fields, unless you know for sure that these columns do not have different values:

 SELECT CandEmail, min(CandName) CandName, min(EmployerId) EmployerId, MIN(ContNum) ContNum, -- or you can use MAX() min(IsDeleted) IsDeleted, min(CandPortalId) CandPortalId FROM tbl1 WHERE EmployerId = 7 AND IsDeleted = 0 GROUP BY CandEmail 

Answer 2

You can use ROW_NUMBER with an 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)

Defines the splitting and ordering of the rowset before using the related window function. That is, the OVER clause defines a window or a user-defined set of lines as a result of a request to set. Then the window function calculates the value for each row in the window.

Answer 3

Use group by : -

  SELECT CandEmail, MAX(CandName), MAX(EmployerId), MAX(ContNum), MAX(IsDeleted), MAX(CandPortalId) FROM tbl1 WHERE EmployerId = 7 AND IsDeleted = 0 GROUP BY CandEmail 

Answer 4

 SELECT CandEmail, MAX(CandName), 7 [EmployerId], MAX(ContNum), 0 [IsDeleted], MAX(CandPortalId) FROM tbl1 WHERE EmployerId = 7 AND IsDeleted = 0 GROUP BY CandEmail