Query only the first detail record for each master record

Question

If I have the following master-detail relationship:

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 perform a query that returns the following result:

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

The query should join the two tables, and sort all the records on the "year" field, but only return the first detail record for each master record. I know how to join the tables and sort on the "year" field, but it's not clear how (or if) I might be able to only retrieve the first joined record for each owner.

Three related questions:

1. Can I perform this kind of query using LINQ-to-SQL?
2. Can I perform the query using T-SQL?
3. Would it be best to just create a stored procedure for the query given its likely complexity?

Answer 1

Using Sql Server 2005+ you can try (Full example)

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

something like this:

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 3

You could use 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 4

c# linq2sql:

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