CODEWITHSUNDEEP
sqlsql-server
Mantas Stanionis
Mantas Stanionis

Reputation: 121

Select Rows without duplicates

I want to select only unique rows, without duplicates. In this case, I only want 4 rows returned. How can I remove the duplicates?

SELECT 
    d.FirstName, d.LastName, d.DateOfBirth, d.Gender, d.SSN, d.Gender, d.Race, 
    d.HobbyInterest, d.PhysicalDescription, pf.[No of Religious Donations]
FROM  
    (SELECT 
         pf.*, 
         COUNT(Religious) OVER (PARTITION BY Person_Id) as 'No of Religious Donations'
     FROM 
         vw_profileFact pf) pf
JOIN 
    vw_dossier d ON pf.Person_Id = d.Person_Id
WHERE 
    [No of Religious Donations] > 2
ORDER BY 
    [No of Religious Donations] DESC;

enter image description here

Upvotes: 0

Views: 178

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

I'm a little confused. Why are you using window functions?

SELECT d.*, pf.num_religious_donations
FROM vw_dossier d JOIN
     (SELECT p.person_id,
             COUNT(p.Religious) AS num_religious_donations
      FROM vw_profileFact p
      GROUP BY p.person_id
     ) pf
     ON pf.Person_Id = d.Person_Id
WHERE num_religious_donations> 2
ORDER BY pf.num_religious_donations DESC;

If you want one row per person, then aggregation is appropriate.

Upvotes: 2

Deepika
Deepika

Reputation: 296

SELECT DISTINCT d.FirstName, d.LastName, d.DateOfBirth, d.Gender, d.SSN, d.Gender, d.Race, d.HobbyInterest, 
d.PhysicalDescription, pf.[No_of_Rel_Donations]
FROM (SELECT pf.*, COUNT(Religious) OVER (PARTITION BY Person_Id) as No_of_Rel_Donations
      FROM vw_profileFact pf
     ) pf
JOIN vw_dossier d 
ON pf.Person_Id = d.Person_Id
WHERE [No of Religious Donations] > 2
ORDER BY [No of Religious Donations] DESC;

Upvotes: 0

Related Questions