SQL SELECT Only the column with max date

Question

I have a table with following data

villageId    ChildID   VC1_date   VC2_date   VC3_date   VC4_date   VC5_date
----------------------------------------------------------------------------
1             1         2/2/2015   2/4/2017   2/8/2013   2/2/2013   2/8/2011
1             2         2/3/2017   2/6/2013   2/3/2015   2/5/2014   2/4/2012
1             3         2/5/2013   2/8/2011   2/1/2017   2/8/2016   2/2/2011
2             1         2/6/2011   2/2/2014   2/2/2012   2/9/2018   2/6/2014
2             2         2/2/2013   2/4/2017   2/7/2011   2/4/2012   2/8/2016
2             3         2/1/2015   2/7/2018   2/9/2014   2/5/2011   2/1/2011

Now i want to select max date of village

VC is short form of Vaccine

My Desired outPut is (Last Vaccinated Date Of Village)

VillageId      Date
--------------------
1              2/4/2017
2              2/9/2018

Hope you understand what i want...

yes duplicate is possible in child table (upper table). but i need distinct values — Zulqarnain Jalil, Jul 24 '17 at 07:30
Have you actually tried [this solution](https://stackoverflow.com/a/6871572/6611487)??. This is the same link provided by both @Sowndarya and fen1x. This would get you `Distinct` values itself. Try it once — boop_the_snoot, Jul 24 '17 at 07:37

score 1 · Accepted Answer · answered Jul 24 '17 at 07:44

This is how I would do it:

declare @vaccines table  
(villageId int,   
ChildID int,
VC1_date date,  
VC2_date date,  
VC3_date date,  
VC4_date date,  
VC5_date date)

INSERT INTO @vaccines VALUES(1, 1, '2015-02-02', '2017-04-02', '2013-08-02', '2013-02-02', '2011-08-02')
INSERT INTO @vaccines VALUES(1, 2, '2017-03-02', '2013-06-02', '2015-03-02', '2014-05-02', '2012-04-02')
INSERT INTO @vaccines VALUES(1, 3, '2013-05-02', '2011-08-02', '2017-01-02', '2016-08-02', '2011-02-02')
INSERT INTO @vaccines VALUES(2, 1, '2011-06-02', '2014-02-02', '2012-02-02', '2018-09-02', '2014-06-02')
INSERT INTO @vaccines VALUES(2, 2, '2013-02-02', '2017-04-02', '2011-07-02', '2012-04-02', '2016-08-02')
INSERT INTO @vaccines VALUES(2, 3, '2015-01-02', '2018-07-02', '2014-09-02', '2011-05-02', '2011-01-02')

SELECT villageId, MAX(vcdate) FROM
(SELECT villageId, ChildID, VC1_date as vcdate from @vaccines 
UNION
SELECT villageId, ChildID, VC2_date as vcdate from @vaccines 
UNION
SELECT villageId, ChildID, VC3_date as vcdate from @vaccines 
UNION
SELECT villageId, ChildID, VC4_date as vcdate from @vaccines 
UNION
SELECT villageId, ChildID, VC5_date as vcdate from @vaccines) g
GROUP BY villageId

SQL SELECT Only the column with max date

1 Answers1