redesign
its 100s of child record per master record.
I can loop one at a time, etc.

Not horrible but I throw away 1000s of rows.


Bernie van't Hof <onrequest@somewhere> wrote:
>Is this at the design stage, or on an existing system?
>
>Sounds like you have a working solution but are worried about 
>efficiency. Is a collection table with 100 entries really worth 
>bothering about?
>
>- Bernie
>On 17/7/20 1:00 pm, Kevin Zawicki wrote:
>> I have two tables A and B.
>> each has a unique part # column
>> 
>> There is item name table with the part # and then two tables with part
#
>> and sub part #
>> 
>> M part# and info
>> A active   part#, subpart#
>> B retired  part#, subpart#
>> 
>> A list of parts, and one table with active subparts of the part, one table
>> with the retired subparts on the part.
>> 
>> subparts are unique and a subpart cannot be in both tables
>> 
>> 
>> each subpart "child table" has a date column
>> each part may have zero to many subparts in either table
>> 
>> I need to get the last x number (3-5) subparts by date from both tables.
>> 
>> example
>> If I have part 12345 and want the get the latest 5 entries by date and
they
>> may be in A or B or both.
>> 
>> 
>> I can query and collect from both tables and then sort and grab top 5.
>> 
>> But this may put 100s of rows in the collection table just to get the
top
>> 5.
>> 
>> Using QBE or local SQL is there a better way to get the latest 5?
>> 
>> Even if I get the latest 5 from A and latest 5 from B (if they have 5)
then
>> latest 5 from collection it would be more efficient than getting the entire
>> set of subparts.
>> 
>> Is there a way to do TOP 5 in local SQL?
>> 
>> 
>> 
>