CODEWITHSUNDEEP
sqllinq-to-sql
Neal Stublen
Neal Stublen

Reputation: 835

Query only the first detail record for each master record

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?

Upvotes: 3

Views: 3711

Answers (6)

Biggles
Biggles

Reputation: 1

Here is a simple solution using a subquery that returns only the oldest car for each owner that has at least one car.

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

Upvotes: 0

Leslie
Leslie

Reputation: 3644

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

Upvotes: 0

Terry
Terry

Reputation: 1992

Sample LINQ/LINQPad script...returns your desired results without massive nested queries for each field on the Autos you would potentially filter/select.

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();

Upvotes: 0

Francisco
Francisco

Reputation: 4101

c# linq2sql:

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

Upvotes: 1

Gary W
Gary W

Reputation: 1904

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

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166506

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

Upvotes: 4

Related Questions