CODEWITHSUNDEEP
sqloracle-database
Ofer
Ofer

Reputation: 5219

How to get rows form table that each email appear one time

i'm having a hard time getting the right row from my table. I'm trying to get some rows by finding emails and i want to get the last email sent to them. My query is:

select *
from persons p2, (
SELECT p.id,p.name,p.email
FROM persons p, emails e
WHERE p.id = e.person_id
) a
where a.email = p2.email

This query return the list of all the email sent to persons.

Can someone help me with this query, the query need to return list of persons with emails, but each email need to appear one time.

Thanks

Upvotes: 1

Views: 98

Answers (3)

DiAngelo
DiAngelo

Reputation: 220

How about this?

select *
from persons, (SELECT p.id, m.mail, max(m.time_sent) 
               from persons as p, emails as m 
               where m.mail = p.mail
               GROUP BY p.id, m.mail) as a
where persons.id = a.id

Upvotes: 0

Andrew Logvinov
Andrew Logvinov

Reputation: 21851

Try this variant:

select p.*, tmp.email
  from persons p, (select person_id, email
                     from emails e1
                    where not exists (
                          select 1
                            from emails e2
                           where e1.person_id = e2.person_id
                             and e1.time_sent < e2.time_sent)) tmp
 where p.id = tmp.person_id;

Upvotes: 2

Arion
Arion

Reputation: 31249

If you DBMS supports ctes you can do something like this:

;WITH CTE
AS
(
SELECT
    RANK() OVER(PARTITION BY person_id ORDER BY time_sent DESC) AS iRank,
    e.email,
    e.person_id
FROM
    email AS e
)
SELECT
    *
FROM
    persons AS p
    LEFT JOIN CTE
        ON p.id=CTE.person_id
        AND CTE.iRank=1

Upvotes: 0

Related Questions