Query only the first detail record for each master record

Posted by Neal S. on Stack Overflow See other posts from Stack Overflow or by Neal S.
Published on 2010-03-08T07:06:05Z Indexed on 2010/03/08 7:21 UTC
Read the original article Hit count: 494

Filed under:
|

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?

© Stack Overflow or respective owner

Related posts about sql

Related posts about linq-to-sql