Display duplicate row indicator and get only one row when duplicate

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 :

  1. Returns only one owner per boat
  2. When multiple owners on a single boat, return the youngest owner.
  3. Display a column to indicate if a boat has multiple owners.

So the following data set when apply that query would produce

enter image description here

I tried

ROW_NUMBER() OVER (PARTITION BY ....

but haven't had much luck so far.



Read more here: https://stackoverflow.com/questions/67011567/display-duplicate-row-indicator-and-get-only-one-row-when-duplicate

Content Attribution

This content was originally published by Fylix at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: