CareerCup

SELECT distinct PARENT_ID,
first_VALUE(ID) OVER(PARTITION BY PARENT_ID ORDER BY AGE) youngest_child,
first_VALUE(ID) OVER(PARTITION BY PARENT_ID ORDER BY AGE desc) eldest_child
FROM CHILD
order by parent_id;

select parent_id,id from child
where (parent_id,age) in 
(select parent_id,max(age) from child group by parent_id
union 
select parent_id,min(age)from child group by parent_id)
order by parent_id,id,age;

create table parent(id int, age int)
create table children (id int, age int,parent_id int)

insert into parent values(1,40),(2,45),(3,50),(4,55)
insert into children values(1,15,1),(2,18,1),(3,20,1),(2,25,4)


select id,age,a.parent_id from children A
join(
select c.parent_id,max(c.age) as old,min(c.age) as younger from children c
group by c.parent_id) b
on A.parent_id = b.parent_id and (a.age = b.old or a.age = b.younger)

SELECT
a.parent_id,
b.id elder_child_id,
c.id younger_child_id
FROM
(
SELECT
parent_id,
MAX( age) elder_child_age,
MIN(age) younger_child_age
FROM
shc_work_tbls.children_table
GROUP BY
parent_id
)a,
shc_work_tbls.children_table b,
shc_work_tbls.children_table c
WHERE a.parent_id=b.parent_id
AND a.parent_id=c.parent_id
AND a.elder_child_age= b.age
AND a.younger_child_age = c.age



---------------------------
Eg:


CREATE MULTISET TABLE shc_work_tbls.children_table (ID INT,age INT,parent_id INT)PRIMARY INDEX(id);
INSERT INTO shc_work_tbls.children_table VALUES(1,22, 91);
INSERT INTO shc_work_tbls.children_table VALUES(2,23, 91);
INSERT INTO shc_work_tbls.children_table VALUES(3,24, 91);
INSERT INTO shc_work_tbls.children_table VALUES(4,25, 91);
INSERT INTO shc_work_tbls.children_table VALUES(5,32, 92);
INSERT INTO shc_work_tbls.children_table VALUES(6,33, 92);
INSERT INTO shc_work_tbls.children_table VALUES(7,34, 92);
INSERT INTO shc_work_tbls.children_table VALUES(8,35, 92);
INSERT INTO shc_work_tbls.children_table VALUES(9,36, 92);
INSERT INTO shc_work_tbls.children_table VALUES(10,12, 93);
INSERT INTO shc_work_tbls.children_table VALUES(11,14, 93);
INSERT INTO shc_work_tbls.children_table VALUES(12,16, 93);
INSERT INTO shc_work_tbls.children_table VALUES(13,22, 94);
INSERT INTO shc_work_tbls.children_table VALUES(14,25, 94);
INSERT INTO shc_work_tbls.children_table VALUES(15,28, 94);



SELECT
a.parent_id,
b.id elder_child_id,
c.id younger_child_id
FROM
(
SELECT
parent_id,
MAX( age) elder_child_age,
MIN(age) younger_child_age
FROM
shc_work_tbls.children_table
GROUP BY
parent_id
)a,
shc_work_tbls.children_table b,
shc_work_tbls.children_table c
WHERE a.parent_id=b.parent_id
AND a.parent_id=c.parent_id
AND a.elder_child_age= b.age
AND a.younger_child_age = c.age



-------------------
parent_id elder_child_id younger_child_id
91 4 1
93 12 10
94 15 13
92 9 5

--------------------------

select p.id, c.id from Parents p inner join Children c on p.id = c.parent_id where p.id = (select parent_id from children where age in (select max(age), min(age) from children group by parent_id));

SELECT distinct PARENT_ID,
first_VALUE(ID) OVER(PARTITION BY PARENT_ID ORDER BY AGE) youngest_child,
first_VALUE(ID) OVER(PARTITION BY PARENT_ID ORDER BY AGE desc) eldest_child
FROM CHILD
order by parent_id;

sel p.id , t.id from parent p
join
( sel id , age , rank() over (age partition by pid order by age desc) as age_rank ,
rank() over (age partition by pid order by age asc) as age_rank1
from child Qualify age_rank = 1 OR age_rank1 = 1) t
on p.id = t.pid

I think it means select children with no both older or younger brothers:

select a.parent_id
from children a
where not exists (
	select 1
	from children b
	where b.parent_id=a.parent_id
	and b.age>a.age
) or not exists (
	select 1
	from children b
	where b.parent_id=a.parent_id
	and b.age<a.age
)

select a.parent_id
,max(case when a.age = mx_age then a.id end) id_max_child
,max(case when a.age = min_age then a.id end) id_min_child
from children a
join
(
select parent_id
,max(age) mx_age
,min(age) min_age
from children
group by parent_id
)b
on a.parent_id = b.parent_id
group by a.parent_id
;

Why it is necessary to join the parents table if the children.parent_id is equal to parents.id?

select parents.id, max(children.age), min(children.age) from children inner join parents where children.parent_id=parents.id group by parents.id;

select p.id , max(c.age) , min(c.age) from
parent p inner join children c on (p.id = c.parent_id) group by p.id;

select parent_id, min(age_rank), max(age_rank) from (select parent_id, rank() over (partition by parent_id order by age) as age_rank
from children) X group by parent_id

select distinct parent_id,
min(age) over(PARTITION BY PARENT_ID) youngest_child,
max(age) over(PARTITION BY PARENT_ID) eldest_child
FROM CHILDREN
order by parent_id;

Assume parents may have:
- no kids and we have to display them;
- twins and we have to display only one row per parent;
- only one kid and we have to report this child as youngest;

SELECT
  p.id,
  q1.mn,
  q1.mx
FROM
  -- starting with parents to show those who has no kids
  parents p 
  LEFT JOIN
  (
    SELECT
      c1.parent_id,
      -- aggregate to avoid twins issue
      MIN(c1.id) mn,
      MAX(c3.id) mx
    FROM
    -- youngest child
      children c1 
    LEFT JOIN children c2 ON c1.parent_id = c2.parent_id AND c1.age > c2.age
    -- oldest child
    FULL JOIN children c3 ON c1.parent_id = c3.parent_id AND c1.age < c3.age
    WHERE
      c2.id   IS NULL
    AND c1.id IS NOT NULL
    GROUP BY
      c1.parent_id
  )
  q1 ON p.id = q1.parent_id
ORDER BY
  id;

create table #Parent (Id INT, Age int)
create table #Child (Id INT, Age int, Parent_Id int)

INSERT INTO #Parent VALUES (1, 55)
INSERT INTO #Parent VALUES (1, 55)
INSERT INTO #Parent VALUES (1, 55)
INSERT INTO #Child VALUES(1, 30, 1)
INSERT INTO #Child VALUES(2, 28, 1)
INSERT INTO #Child VALUES(3, 25, 1)

INSERT INTO #Parent VALUES (2, 45)
INSERT INTO #Parent VALUES (2, 45)
INSERT INTO #Parent VALUES (2, 45)
INSERT INTO #Child VALUES(4, 20, 2)
INSERT INTO #Child VALUES(5, 18, 2)
INSERT INTO #Child VALUES(6, 15, 2)

SELECT DISTINCT p.Id
	,FIRST_VALUE(c.Id) OVER(PARTITION BY p.Id ORDER BY c.Age asc) YoungestChild
	,FIRST_VALUE(c.Id) OVER(PARTITION BY p.Id ORDER BY c.Age desc) EldestChild
FROM #Parent p
	INNER JOIN #Child c
		ON c.Parent_Id = p.Id

DROP TABLE #Parent
DROP TABLE #Child

select pid,min(age),max(age) from child group by pid;

create table Parent
(id int identity(1,1)
, age int)
Go
create table Children
(id int identity(1,1)
,id_parent int
, age int)
Go

insert Parent values(50),(60),(25),(42),(38)
go
insert Children values(1, 20),(1,10),(1,25),(2,2),(3,8)
go



select id, childid, age from(
select p.id, c.id childid,c.age,  min(c.age) over(partition by p.id) young,  max(c.age) over(partition by p.id) old
from Parent p inner join Children c
on p.id=c.id_parent)A 
where exists(select * from Children ch where ch.id=A.childid and ((ch.age=young) or (ch.age=old)))

select distinct y.parent_id,
case when y.age = x.o_age then y.id end as oldest,
case when y.age = x.y_age then y.id end as youngest
from children y
join (
select parent_id,
max(age) as o_age,
min(age) as y_age
from children
group by parent_id
) x on y.parent_id = x.parent_id

SELECT c.p_id AS ParentID, c1.id AS MaxAgeChild_ID , c2.id AS MinAgeChild_ID FROM children c1
INNER JOIN
(select parent_id AS p_id, max(children.age) AS maxChild_age, min(children.age) AS minChild_age from children group by parent_id) c
ON (c1.age = c.maxChild_age AND c1.parent_id = c.p_id)
INNER JOIN children c2
ON (c2.age = c.minChild_age AND c2.parent_id = c.p_id);