Select value from table based on conditions in other rows

Question

I have multiple rows with a parent ID that associates related rows. I want to select the email address where Status = 'active', for the parent ID, and if there's multiple rows with that condition, I want to pick the most recently modified (createDate).

Basically I have two+ records, parent ID 111. The first record has m@x.com with a status of 'active', and the second record has m@y.com with a status of 'unsubscribed'. How do I select just ID 111 with m@x.com?

How would I go about this?

Table Data:

ID      ParentID  Email          Status   CreateDate
1000919  1000919  xxx@gmail.com   bounced   2/5/18
1017005  1000919  yyy@gmail.com   active    1/6/18
1002868  1002868  sss@gmail.com   active    12/31/17
1002868  1002868  www@gmail.com   active    12/31/17
1002982  1002982  uuu@gmail.com   held      2/7/18
1002982  1002982  iii@gmail.com   held      2/7/18
1002990  1002990  ooo@gmail.com   active    10/26/18
1003255  1003255  ppp@gmail.com   active    2/7/18

Expected Result:

 ParentID  Email          Status   CreateDate
 1000919   yyy@gmail.com   active   1/6/18
 1002868   sss@gmail.com   active   12/31/17

Add some more rows of sample data, and also the specified result - as formatted text, not images. Also show us your current query attempt. — jarlh, Mar 08 '18 at 20:22
Your description and your sample data are nothing like each other. This makes it really difficult to understand the problem at hand. — Sean Lange, Mar 08 '18 at 20:25
Possible duplicate of [Retrieving the last record in each group](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) — Tab Alleman, Mar 08 '18 at 20:27

score 0 · Answer 1 · answered Mar 08 '18 at 20:41

I hope this is what you need:

SELECT * FROM table 
WHERE parent_id IN 
(SELECT id FROM users WHERE status = "active") 
ORDER BY createdate DESC LIMIT 1;

Order by createdate in descending order will allow you to select only last n rows, where n is set in LIMIT.

Cesar Feliz Damiron · Answer 2 · 2018-03-09T03:34:52.417

ok, so based on the information of the question.

1. A self-reference field exists on the table.
1. The status has to be active.
1. If 2 or more records exist in the table for the same parent take the latest.
1. I added a 4th if matching all conditions to take the highest email.
1. the code is not formatting properly I'm new to the stackoverflow (lol)

Blockquote table to create

create table #tmp
(id int identity,
name varchar(50),
email varchar(50),
status varchar(20),
add_date datetime,
mod_date datetime,
account_id int)

Blockquote Populating table

insert into #tmp 
(name,email,status, add_date, mod_date, account_id)
values ('Cesar', 'Cesar@hotmail.com', 'Active', '20180101', '20180103', 1),
('manuel', 'manuel@hotmail.com', 'Active', '20180103', '20180103', 1),
('feliz', 'feliz@hotmail.com', 'Inactive', '20180103', '20180105', 1),
('lucien', 'lucien@hotmail.com', 'Active', '20180105', '20180105', 2),
('norman', 'norman@hotmail.com', 'Active', '20180110', '20180110', 2),
('tom', 'tom@hotmail.com', 'Active', '20180110', '20180115', 3),
('peter', 'peter@hotmail.com', 'inactive', '20180101', '20180110', 3),
('john', 'john@hotmail.com', 'Active', '20180101', '20180105', 3)

Blockquote Visualization

select * 
from #tmp as a
where status = 'Active' and
exists (select 
            account_id 
        from #tmp as b 
        where 
            b.status = a.status 
        group by 
            account_id 
        having 
            MAX(b.mod_date) = a.mod_date and 
            a.email = MAX(b.email))

Blockquote, so the exists, is faster than having a subquery to predicate the data since the table would be pulled back in full

score 0 · Answer 3 · answered Mar 08 '18 at 23:02

This is really hard with no primary key and duplicate rows. You have no defined the answer when a parentid has 2 rows on the same date and different emails. CreatedDate could be a datetime field, and likely to be unique. Without that we must use >=. This will do it though

> SELECT distinct a.*  FROM [Temp] a join [Temp] b 
>      on a.parentid=b.parentid 
>      and a.createdate >= b.createdate 
>      and a.status='active' and b.status='active'

Select value from table based on conditions in other rows

3 Answers3