How can i just select the top 1 record from a child table

Question

Post reply

How can i just select the top 1 record from a child table

justy

Old Hand

Points: 371
More actions
November 30, 2005 at 3:55 am

#92318

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!!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

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.

justy Old Hand Points: 371 More actions · Answer 2

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.

venkat-216866 SSC Veteran Points: 257 More actions · Answer 3

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...

justy Old Hand Points: 371 More actions · Answer 4

That works! Thanks grasshopper!!

Much appreciated.

David Burrows SSC Guru Points: 65142 More actions · Answer 5

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.

justy Old Hand Points: 371 More actions · Answer 6

Sorry. I did have to change the join to a left outer join.