CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Catherine Tyler
Catherine Tyler

Reputation: 85

How to retrieve DISTINCT values from this table?

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 https://i.sstatic.net/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.

Upvotes: 4

Views: 136

Answers (4)

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

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

Upvotes: 2

Tim Schmelter
Tim Schmelter

Reputation: 460038

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.

Upvotes: 5

Taryn
Taryn

Reputation: 247640

If you do not care which ContNum you get, then you can place an aggregate function around that field and then GROUP BY the 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 use aggregate functions on the other fields as well, unless you know for sure there are not different values in those columns:

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

Upvotes: 1

Paul Fleming
Paul Fleming

Reputation: 24526

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

Upvotes: 1

Related Questions