SQL query without repeated element

Asked

Viewed 51 times

0

I have this query in sql

SELECT 
    "Certificates"."Email", 
    "Organizations"."CommonName", 
    "Certificates"."Id", 
    "Certificates"."OrganizationId"
FROM
    "Certificates"
INNER JOIN 
    "Organizations" ON "Organizations"."Id" = "Certificates"."OrganizationId"
ORDER BY 
    "Email"
LIMIT 
     15
OFFSET 
     0

However it returns me all the certificates, being that there are certificates with repeated emails, I wish it were only single emails, I tried to use DISTINCT after SELECT, but it keeps returning the same thing

sql

1 answer

1


The behavior of your query is correct. It is likely that Id of your table does not repeat, so returns the data without grouping even with Distinct.

To return the results in a grouped form, use the query below.

SELECT     
    "Certificates"."Email", 
    "Organizations"."CommonName", 
     MAX("Certificates"."Id"), 
    "Certificates"."OrganizationId"
FROM
    "Certificates"
INNER JOIN 
    "Organizations" ON "Organizations"."Id" = "Certificates"."OrganizationId"
GROUP BY
   "Certificates"."Email", 
    "Organizations"."CommonName",
    "Certificates"."OrganizationId"
ORDER BY 
    "Email"
LIMIT 
     15

  • In fact with this query it returns without repeated emails, but I am without the Id, and in my application I will use the Id too.

  • And if possible at the time of comparing 2 different emails, he takes what has the largest Id

  • Yes, I updated the answer.

  • Sensational, thanks for the help

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.