Selecting TOP X child records for a parent record

[Catch_22]

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_AuditQualifie dNumberExtractD etails
(BatchNumber,
EntryRecordID,
LN,
AdditionalQualC ritPassed)
(select t1.BatchNumber,
t1.EntryRecordI D,
t1.LN,
t1.AdditionalQu alCritPassed
from
(select BatchNumber,
RecordType,
EntryRecordID,
LN,
AdditionalQualC ritPassed
from t_AuditQualifie dNumberExtractD etails_Temp) as t1
inner join
(select BatchNumber,
RecordType,
EntryRecordID,
Count(*) as AssignedNumbers ,
max(TotalNumber s) as TotalNumbers
from t_AuditQualifie dNumberExtractD etails_Temp
group by BatchNumber, RecordType, EntryRecordID
having count(*) = max(TotalNumber s)) as t2
on t1.BatchNumber = t2.BatchNumber
and t1.RecordType = t2.RecordType
and t1.EntryRecordI D = t2.EntryRecordI D)

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_Qualificatio nMismatchedAllo cs
([BatchNumber],
[RecordType],
[EntryRecordID],
[AssignedNumbers],
[TotalNumbers])
(select BatchNumber,
RecordType,
EntryRecordID,
Count(*) as AssignedNumbers ,
max(TotalNumber s) as TotalNumbers
from t_AuditQualifie dNumberExtractD etails_Temp
group by BatchNumber, RecordType, EntryRecordID
having count(*) <max(TotalNumbe rs))

SELECT @QualificationM ismatched_RowCn t = 1

SELECT @MaxQualificati onMismatched = (select count(*) from
@t_Qualificatio nMismatchedAllo cs)

while @QualificationM ismatched_RowCn t <= @MaxQualificati onMismatched
begin
--## Get Prize Draw to extract numbers for
select @RecordType = RecordType,
@EntryRecordID = EntryRecordID,
@AssignedNumber s = AssignedNumbers ,
@TotalNumbers = TotalNumbers
from @t_Qualificatio nMismatchedAllo cs
where QualMismatchedA llocsRowNum = @QualificationM ismatched_RowCn t

SET ROWCOUNT @TotalNumbers

insert into t_AuditQualifie dNumberExtractD etails
(BatchNumber,
EntryRecordID,
LN,
AdditionalQualC ritPassed)
(select BatchNumber,
EntryRecordID,
LN,
AdditionalQualC ritPassed
from t_AuditQualifie dNumberExtractD etails_Temp
where RecordType = @RecordType
and EntryRecordID = @EntryRecordID)

SET @QualificationM ismatched_RowCn t =

QualificationMi smatched_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 ?

[Roy Harvey]

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 QualMismatchedA llocsRowNum in table
t_AuditQualifie dNumberExtractD etails_Temp is an identity column or
other unique value, I think this can replace the entire loop process.

INSERT INTO t_AuditQualifie dNumberExtractD etails
(BatchNumber,
EntryRecordID,
LN,
AdditionalQualC ritPassed)
SELECT BatchNumber,
EntryRecordID,
LN,
AdditionalQualC ritPassed
FROM t_AuditQualifie dNumberExtractD etails_Temp as A
WHERE TotalNumbers <=
(select count(*)
from t_AuditQualifie dNumberExtractD etails_Temp as B
where A.BatchNumber = B.BatchNumber
and A.RecordType = B.RecordType
and A.EntryRecordID = B.EntryRecordID
and A.QualMismatche dAllocsRowNum <=
B.QualMismatche dAllocsRowNum)

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

I take it that QualMismatchedA llocsRowNum in table
@t_Qualificatio nMismatchedAllo cs 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(TotalNumber s)) as t2
having count(*) <max(TotalNumbe rs))

brings up a question. What sort of row count do we get from each of
the three relationships?

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

The reason I ask is that I do not see a difference in the final
results between count(*) < max(TotalNumber s) 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(TotalNumber s)) than should be inserted. If I have not
missed something, and if a significant part of the processing is for
count(*) < max(TotalNumber s), then the first test could be modified
to:

having count(*) <= max(TotalNumber s)) as t2

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

having count(*) max(TotalNumber s))

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********@yah oo.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_AuditQualifie dNumberExtractD etails
(BatchNumber,
EntryRecordID,
LN,
AdditionalQualC ritPassed)
(select t1.BatchNumber,
t1.EntryRecordI D,
t1.LN,
t1.AdditionalQu alCritPassed
from
(select BatchNumber,
RecordType,
EntryRecordID,
LN,
AdditionalQualC ritPassed
from t_AuditQualifie dNumberExtractD etails_Temp) as t1
inner join
(select BatchNumber,
RecordType,
EntryRecordID,
Count(*) as AssignedNumbers ,
max(TotalNumber s) as TotalNumbers
from t_AuditQualifie dNumberExtractD etails_Temp
group by BatchNumber, RecordType, EntryRecordID
having count(*) = max(TotalNumber s)) as t2
on t1.BatchNumber = t2.BatchNumber
and t1.RecordType = t2.RecordType
and t1.EntryRecordI D = t2.EntryRecordI D)

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_Qualificatio nMismatchedAllo cs
([BatchNumber],
[RecordType],
[EntryRecordID],
[AssignedNumbers],
[TotalNumbers])
(select BatchNumber,
RecordType,
EntryRecordID,
Count(*) as AssignedNumbers ,
max(TotalNumber s) as TotalNumbers
from t_AuditQualifie dNumberExtractD etails_Temp
group by BatchNumber, RecordType, EntryRecordID
having count(*) <max(TotalNumbe rs))

SELECT @QualificationM ismatched_RowCn t = 1

SELECT @MaxQualificati onMismatched = (select count(*) from
@t_Qualificati onMismatchedAll ocs)

while @QualificationM ismatched_RowCn t <= @MaxQualificati onMismatched
begin
--## Get Prize Draw to extract numbers for
select @RecordType = RecordType,
@EntryRecordID = EntryRecordID,
@AssignedNumber s = AssignedNumbers ,
@TotalNumbers = TotalNumbers
from @t_Qualificatio nMismatchedAllo cs
where QualMismatchedA llocsRowNum = @QualificationM ismatched_RowCn t

SET ROWCOUNT @TotalNumbers

insert into t_AuditQualifie dNumberExtractD etails
(BatchNumber,
EntryRecordID,
LN,
AdditionalQualC ritPassed)
(select BatchNumber,
EntryRecordID,
LN,
AdditionalQualC ritPassed
from t_AuditQualifie dNumberExtractD etails_Temp
where RecordType = @RecordType
and EntryRecordID = @EntryRecordID)

SET @QualificationM ismatched_RowCn t =

QualificationM ismatched_RowCn t + 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 ?

[Roy Harvey]

Hopefully you saw my response to your other message.

Roy Harvey
Beacon Falls, CT