I built the schema at http://sqlfiddle.com/#!18/7e9e3
CREATE TABLE BoatOwners ( BoatID INT, OwnerDOB DATETIME, Name VARCHAR(200) ); INSERT INTO BoatOwners (BoatID, OwnerDOB,Name) VALUES (1, '2021-04-06', 'Bob1'), (1, '2020-04-06', 'Bob2'), (1, '2019-04-06', 'Bob3'), (2, '2012-04-06', 'Tom'), (3, '2009-04-06', 'David'), (4, '2006-04-06', 'Dale1'), (4, '2009-04-06', 'Dale2'), (4, '2013-04-06', 'Dale3');
I would like to write a query that would produce the following result characteristics :
- Returns only one owner per boat
- When multiple owners on a single boat, return the youngest owner.
- Display a column to indicate if a boat has multiple owners.
So the following data set when apply that query would produce
ROW_NUMBER() OVER (PARTITION BY ....
but haven't had much luck so far.