agarwala.uw
BAN USERcreate table #Parent (Id INT, Age int, Child_Id int)
create table #Child (Id INT, Age int, Parent_Id int)
INSERT INTO #Parent VALUES (1, 55, 1)
INSERT INTO #Parent VALUES (1, 55, 2)
INSERT INTO #Parent VALUES (1, 55, 3)
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, 4)
INSERT INTO #Parent VALUES (2, 45, 5)
INSERT INTO #Parent VALUES (2, 45, 6)
INSERT INTO #Child VALUES(4, 20, 2)
INSERT INTO #Child VALUES(5, 18, 2)
INSERT INTO #Child VALUES(6, 15, 2)
;WITH T AS
(SELECT 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
,ROW_NUMBER() OVER(PARTITION BY p.Id ORDER BY c.Age desc) r
FROM #Parent p
INNER JOIN #Child c
ON p.Child_Id = c.Id)
SELECT Id, YoungestChild, EldestChild FROM T WHERE r = 1
DROP TABLE #Parent
DROP TABLE #Child
create table #TempTab (Name varchar(100), MName varchar(100), LName varchar(100))
insert into #TempTab VALUES('John', 'A', 'Tree')
insert into #TempTab VALUES('John', 'B', 'Apple')
insert into #TempTab VALUES('Linda', 'C', 'Orange')
insert into #TempTab VALUES('Mark', 'D', 'Honda')
insert into #TempTab VALUES('Rob', 'E', 'Boeing')
select MAX(LEN(CONCAT(Name, MName, LName))) FROM #TempTab
create table #TempTab (Name varchar(100), Decision bit, PollDate Date)
insert into #TempTab VALUES('John', 1, '1 Jan 2016')
insert into #TempTab VALUES('John', 0, '2 Jan 2016')
insert into #TempTab VALUES('Linda', 1, '1 Jan 2016')
insert into #TempTab VALUES('Mark', 1, '5 Jan 2016')
insert into #TempTab VALUES('Rob', 0, '5 Jan 2016')
;WITH T AS
(select *
, ROW_NUMBER() OVER(PARTITION BY Name ORDER BY PollDate desc) r
from #TempTab)
SELECT Name
FROM T
WHERE r = 1
and Decision = 1
- agarwala.uw November 03, 2016