By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,903 Members | 1,977 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,903 IT Pros & Developers. It's quick & easy.

Selecting TOP X child records for a parent record

P: n/a
Hi,

I have a stored procedure that has to extract the child records for
particular parent records.

The issue is that in some cases I do not want to extract all the child
records only a certain number of them.

Firstly I identify all the parent records that have the requird number
of child records and insert them into the result table.

insert into t_AuditQualifiedNumberExtractDetails
(BatchNumber,
EntryRecordID,
LN,
AdditionalQualCritPassed)
(select t1.BatchNumber,
t1.EntryRecordID,
t1.LN,
t1.AdditionalQualCritPassed
from
(select BatchNumber,
RecordType,
EntryRecordID,
LN,
AdditionalQualCritPassed
from t_AuditQualifiedNumberExtractDetails_Temp) as t1
inner join
(select BatchNumber,
RecordType,
EntryRecordID,
Count(*) as AssignedNumbers,
max(TotalNumbers) as TotalNumbers
from t_AuditQualifiedNumberExtractDetails_Temp
group by BatchNumber, RecordType, EntryRecordID
having count(*) = max(TotalNumbers)) as t2
on t1.BatchNumber = t2.BatchNumber
and t1.RecordType = t2.RecordType
and t1.EntryRecordID = t2.EntryRecordID)

then insert the remaining records into a temp table where the number of
records required does not equal the total number of child records, and
thenloop through each record manipulating the ROWNUMBER to only select
the number of child records needed.

insert into @t_QualificationMismatchedAllocs
([BatchNumber],
[RecordType],
[EntryRecordID],
[AssignedNumbers],
[TotalNumbers])
(select BatchNumber,
RecordType,
EntryRecordID,
Count(*) as AssignedNumbers,
max(TotalNumbers) as TotalNumbers
from t_AuditQualifiedNumberExtractDetails_Temp
group by BatchNumber, RecordType, EntryRecordID
having count(*) <max(TotalNumbers))

SELECT @QualificationMismatched_RowCnt = 1

SELECT @MaxQualificationMismatched = (select count(*) from
@t_QualificationMismatchedAllocs)

while @QualificationMismatched_RowCnt <= @MaxQualificationMismatched
begin
--## Get Prize Draw to extract numbers for
select @RecordType = RecordType,
@EntryRecordID = EntryRecordID,
@AssignedNumbers = AssignedNumbers,
@TotalNumbers = TotalNumbers
from @t_QualificationMismatchedAllocs
where QualMismatchedAllocsRowNum = @QualificationMismatched_RowCnt

SET ROWCOUNT @TotalNumbers

insert into t_AuditQualifiedNumberExtractDetails
(BatchNumber,
EntryRecordID,
LN,
AdditionalQualCritPassed)
(select BatchNumber,
EntryRecordID,
LN,
AdditionalQualCritPassed
from t_AuditQualifiedNumberExtractDetails_Temp
where RecordType = @RecordType
and EntryRecordID = @EntryRecordID)

SET @QualificationMismatched_RowCnt =

QualificationMismatched_RowCnt + 1
SET ROWCOUNT 0
end

Is there a better methodology for doing this .....

Is the use of a table variable here incorrect ?

Should I be using a temporary table or indexed table if there are a
large number of parent records where the child records required does
not match the total number of child records ?

Oct 29 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Looping is always to be avoided if possible. What version of SQL
Server are you running? In the (unlikely) event it is 2005, I think
it should be possible to eliminate the looping, as TOP accepts a
variable in 2005, rather than requiring a constant.

Even with other releases I think I see a way to eliminate the loop.
Assuming that QualMismatchedAllocsRowNum in table
t_AuditQualifiedNumberExtractDetails_Temp is an identity column or
other unique value, I think this can replace the entire loop process.

INSERT INTO t_AuditQualifiedNumberExtractDetails
(BatchNumber,
EntryRecordID,
LN,
AdditionalQualCritPassed)
SELECT BatchNumber,
EntryRecordID,
LN,
AdditionalQualCritPassed
FROM t_AuditQualifiedNumberExtractDetails_Temp as A
WHERE TotalNumbers <=
(select count(*)
from t_AuditQualifiedNumberExtractDetails_Temp as B
where A.BatchNumber = B.BatchNumber
and A.RecordType = B.RecordType
and A.EntryRecordID = B.EntryRecordID
and A.QualMismatchedAllocsRowNum <=
B.QualMismatchedAllocsRowNum)

That would require a good index on the columns (BatchNumber,
RecordType, EntryRecordID).

I take it that QualMismatchedAllocsRowNum in table
@t_QualificationMismatchedAllocs is an identity column? Are the
values always starting at 1? My concern is looping through a lot of
numbers that might not exist in the table.

Looking at the tests that split the data into two categories:
>having count(*) = max(TotalNumbers)) as t2
having count(*) <max(TotalNumbers))
brings up a question. What sort of row count do we get from each of
the three relationships?

count(*) = max(TotalNumbers)
count(*) < max(TotalNumbers) -- If this is significant, see below.
count(*) max(TotalNumbers)

The reason I ask is that I do not see a difference in the final
results between count(*) < max(TotalNumbers) and when they are
equal. In both those cases all the rows are used. The looping
process appears to me to only be required when there are MORE rows
(count(*) max(TotalNumbers)) than should be inserted. If I have not
missed something, and if a significant part of the processing is for
count(*) < max(TotalNumbers), then the first test could be modified
to:

having count(*) <= max(TotalNumbers)) as t2

And the one used to generate the exceptions used in the loop changed
to:

having count(*) max(TotalNumbers))

Which would cut down on the looping (or the alternate joining
proposed) process.

Roy Harvey
Beacon Falls, CT

On 28 Oct 2006 23:26:51 -0700, "Catch_22" <ca********@yahoo.co.uk>
wrote:
>Hi,

I have a stored procedure that has to extract the child records for
particular parent records.

The issue is that in some cases I do not want to extract all the child
records only a certain number of them.

Firstly I identify all the parent records that have the requird number
of child records and insert them into the result table.

insert into t_AuditQualifiedNumberExtractDetails
(BatchNumber,
EntryRecordID,
LN,
AdditionalQualCritPassed)
(select t1.BatchNumber,
t1.EntryRecordID,
t1.LN,
t1.AdditionalQualCritPassed
from
(select BatchNumber,
RecordType,
EntryRecordID,
LN,
AdditionalQualCritPassed
from t_AuditQualifiedNumberExtractDetails_Temp) as t1
inner join
(select BatchNumber,
RecordType,
EntryRecordID,
Count(*) as AssignedNumbers,
max(TotalNumbers) as TotalNumbers
from t_AuditQualifiedNumberExtractDetails_Temp
group by BatchNumber, RecordType, EntryRecordID
having count(*) = max(TotalNumbers)) as t2
on t1.BatchNumber = t2.BatchNumber
and t1.RecordType = t2.RecordType
and t1.EntryRecordID = t2.EntryRecordID)

then insert the remaining records into a temp table where the number of
records required does not equal the total number of child records, and
thenloop through each record manipulating the ROWNUMBER to only select
the number of child records needed.

insert into @t_QualificationMismatchedAllocs
([BatchNumber],
[RecordType],
[EntryRecordID],
[AssignedNumbers],
[TotalNumbers])
(select BatchNumber,
RecordType,
EntryRecordID,
Count(*) as AssignedNumbers,
max(TotalNumbers) as TotalNumbers
from t_AuditQualifiedNumberExtractDetails_Temp
group by BatchNumber, RecordType, EntryRecordID
having count(*) <max(TotalNumbers))

SELECT @QualificationMismatched_RowCnt = 1

SELECT @MaxQualificationMismatched = (select count(*) from
@t_QualificationMismatchedAllocs)

while @QualificationMismatched_RowCnt <= @MaxQualificationMismatched
begin
--## Get Prize Draw to extract numbers for
select @RecordType = RecordType,
@EntryRecordID = EntryRecordID,
@AssignedNumbers = AssignedNumbers,
@TotalNumbers = TotalNumbers
from @t_QualificationMismatchedAllocs
where QualMismatchedAllocsRowNum = @QualificationMismatched_RowCnt

SET ROWCOUNT @TotalNumbers

insert into t_AuditQualifiedNumberExtractDetails
(BatchNumber,
EntryRecordID,
LN,
AdditionalQualCritPassed)
(select BatchNumber,
EntryRecordID,
LN,
AdditionalQualCritPassed
from t_AuditQualifiedNumberExtractDetails_Temp
where RecordType = @RecordType
and EntryRecordID = @EntryRecordID)

SET @QualificationMismatched_RowCnt =

QualificationMismatched_RowCnt + 1
SET ROWCOUNT 0
end

Is there a better methodology for doing this .....

Is the use of a table variable here incorrect ?

Should I be using a temporary table or indexed table if there are a
large number of parent records where the child records required does
not match the total number of child records ?
Oct 29 '06 #2

P: n/a

Hopefully you saw my response to your other message.

Roy Harvey
Beacon Falls, CT
Oct 30 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.