Request only the first detailed record for each master record

Question

If I have the following master part relationships:

owner_tbl auto_tbl
--------- --------
owner ---> owner
              auto
              year

And I have the following table data:

owner_tbl auto_tbl
--------- --------
john john, corvette, 1968
              john, prius, 2008
james james f-150 2004
              james cadillac 2002
              james, accord, 2009
jeff jeff, tesla, 2010
              jeff, hyundai, 1996

Now I want to execute a query that returns the following result:

john, corvette, 1968
jeff, hyundai, 1996
james cadillac 2002

"", . , "", , ( ) .

:

• LINQ-to-SQL?
• T-SQL?
• , ?

Answer 1

Sql Server 2005+, ( )

DECLARE @owner_tbl TABLE(
        [owner] VARCHAR(50)
)

DECLARE @auto_tbl TABLE(
        [owner] VARCHAR(50),
        [auto] VARCHAR(50),
        [year]VARCHAR(4)
)

INSERT INTO @owner_tbl SELECT 'john'
INSERT INTO @owner_tbl SELECT 'james'
INSERT INTO @owner_tbl SELECT 'jeff'        

INSERT INTO @auto_tbl SELECT 'john','corvette','1968'
INSERT INTO @auto_tbl SELECT 'john','prius','2008'
INSERT INTO @auto_tbl SELECT 'james','f-150','2004'
INSERT INTO @auto_tbl SELECT 'james','cadillac','2002'
INSERT INTO @auto_tbl SELECT 'james','accord','2009'
INSERT INTO @auto_tbl SELECT 'jeff','tesla','2010'
INSERT INTO @auto_tbl SELECT 'jeff','hyundai','1996'

;WITH Autos AS(
        SELECT  *,
                ROW_NUMBER() OVER(PARTITION BY a.owner ORDER BY a.year) ROWID
        FROM    @auto_tbl a
)
SELECT  *
FROM    Autos
WHERE   ROWID = 1
ORDER BY owner

Answer 2

cross apply:

SELECT Owner, X.Car,X.Year
FROM Owener_tbl O
CROSS APPLY(
SELECT TOP 1 * FROM Auto_tbl WHERE Owner=O.Owner ORDER BY Year ASC
) X

Answer 3

# linq2sql:

var results = db.owner_tbls.Select(p=>p.auto_tbls.OrderBy(q=>q.year).First());

Answer 4

- :

SELECT o.owner, a.auto, a.EarliestYear
FROM owner_tbl o
INNER JOIN (SELECT owner, auto, min(year) As EarliestYear FROM auto_tbl 
GROUP BY owner, auto) a ON o.owner = a.owner

Answer 5

LINQ/LINQPad script... Autos, /.

var Owners = new [] { new { Name = "John" }, new { Name = "James" }, new { Name = "Jeff" } };
var Autos = new [] { 
    new { Owner = "John", Auto = "Corvette", Year = 1968 },
    new { Owner = "John", Auto = "Prius", Year = 2008 },
    new { Owner = "James", Auto = "F-150", Year = 2004 },
    new { Owner = "James", Auto = "Cadillac", Year = 2002 },
    new { Owner = "James", Auto = "Accord", Year = 2009 },
    new { Owner = "Jeff", Auto = "Tesla", Year = 2010 },
    new { Owner = "Jeff", Auto = "Hyundai", Year = 1996 }
};

var results = from o in Owners
              join c in Autos.OrderBy( a => a.Year )
                             .GroupBy( g => g.Owner )
                             .Select( a => a.FirstOrDefault() )
                     on o.Name equals c.Owner
              orderby o.Name descending
              select new { o.Name, c.Auto, c.Year };

results.Dump();

Answer 6

, , , .

SELECT owner_tbl.owner
, auto_tbl.auto
, auto_tbl.year
FROM owner_tbl
, auto_tbl
WHERE owner_tbl.owner = auto_tbl.owner
AND auto_tbl.auto IN
  (SELECT TOP 1 auto 
  FROM auto_tbl 
  WHERE owner = owner_tbl.owner
  ORDER BY year)
ORDER BY owner_tbl.owner