How can i just select the top 1 record from a child table
-
Hi,
I have two relating tables joined on id. The child table can have none, one or more records relating to the parent table, and contains a recency date column.
How can i join the tables and only select the most recent record from the child table?
I don't really want to write a function to do this. I was hoping to be able to do it all in one SELECT statement.
Help!!
-
You may need to give us more information, but based upon what your post states, this should work...
CREATE TABLE #Parent( IdentityField integer,
OtherField varchar(15))
INSERT INTO #Parent
VALUES( 1, 'First Value')
INSERT INTO #Parent
VALUES( 2, 'Second Value')
CREATE TABLE #Child( IdentityField integer,
ForeignKey integer,
RecencyDate smalldatetime)
INSERT INTO #Child
VALUES( 1, 1, '01/01/2005')
INSERT INTO #Child
VALUES( 2, 1, '07/13/2005')
INSERT INTO #Child
VALUES( 3, 1, '12/31/2005')
INSERT INTO #Child
VALUES( 4, 2, '01/01/2005')
INSERT INTO #Child
VALUES( 5, 2, '07/13/2005')
INSERT INTO #Child
VALUES( 6, 2, '12/31/2005')
INSERT INTO #Child
VALUES( 7, 2, '01/01/2006')
SELECT P.IdentityField AS Parent_IdentityField,
C.ForeignKey AS Child_ForeignKey,
P.OtherField AS Parent_OtherField,
C.MaxRecencyDate AS Child_MaxRecencyDate
FROM #Parent P
INNER JOIN( SELECT ForeignKey, MAX( RecencyDate) AS MaxRecencyDate FROM #Child GROUP BY ForeignKey) C
ON( P.IdentityField = C.ForeignKey)
DROP TABLE #Parent
DROP TABLE #Child
I wasn't born stupid - I had to study.
-
Thank Farrell!
That's on the right tracks, but not quite what i need - I should of explained more.
I have a table of company names and a child table of previous names. A company can have no previous name or many previous names. I need to make a selection of company name and most recent previous name. If the company has no previous name, then I just want a NULL value in previous name. I've modified your example and included the result set I want to see. I just don't know how to achieve it.
- CREATE TABLE #Parent( IdentityField integer,
OtherField varchar(15))
INSERT INTO #Parent
VALUES( 1, 'First Value')
INSERT INTO #Parent
VALUES( 2, 'Second Value')
- INSERT INTO #Parent
VALUES( 3, 'Third Value')
- CREATE TABLE #Child( IdentityField integer,
ForeignKey integer,
RecencyDate smalldatetime,
- Previous varchar(20))
INSERT INTO #Child
VALUES( 1, 1, '01/01/2005', 'A')
INSERT INTO #Child
VALUES( 2, 1, '07/13/2005', 'B')
INSERT INTO #Child
VALUES( 3, 1, '12/31/2005', 'C')
INSERT INTO #Child
VALUES( 4, 2, '01/01/2005', 'D')
INSERT INTO #Child
VALUES( 5, 2, '07/13/2005', 'E')
INSERT INTO #Child
VALUES( 6, 2, '12/31/2005', 'F')
INSERT INTO #Child
VALUES( 7, 2, '01/01/2006', 'G')
What i need to see is this:
- 1 1 First Value 2005-12-31 00:00:00 C
2 2 Second Value 2006-01-01 00:00:00 G
- 3 NULL Third Value NULL NULL
Hope this makes sense.
- CREATE TABLE #Parent( IdentityField integer,
-
select * from #Parent p join #Child c on p.IdentityField = c.ForeignKey and recencydate =
(select max(recencydate) from #Child d where d.ForeignKey= p.IdentityField)
I hope this will do...
-
That works! Thanks grasshopper!!
Much appreciated.
-
Really
I don't think so 
You stated that a parent may be present but no child yet you still wanted to show the parent
inner is the default for join and therefore will only show parent where there is a least one child
change the join to a LEFT OUTER JOIN
Far away is close at hand in the images of elsewhere.
Anon. -
Sorry. I did have to change the join to a left outer join.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply