Re: Best method to SELECT TOP n TO m of a table

"Will Clark" <reply_only_to_newsgroup_please_at_noone-lives-here.com> wrote in message news:bo8qio$4ri$1_at_sparta.btinternet.com...
> > Could you please post that more complicated query?
>
> The more complicated query just has lots of WHERE qualifiers...
>
> SELECT * FROM tblOne WHERE id IN
> (SELECT TOP 3 id FROM tblTwo WHERE
> id NOT IN (SELECT TOP 5 id FROM tblTwo WHERE
> fieldA = 'value' AND
> fieldB = 'value' AND
> (fieldC = 'value' OR fieldD =
> 'value')
> ORDER BY id) AND
> fieldA = 'value' AND
> fieldB = 'value' AND
> (fieldC = 'value' OR fieldD = 'value')
>
> ORDER BY id)
>
> AND (flags = 'A' OR flags = 'B')

You can unnest the innermost subquery. Your second level nested subquery

 SELECT TOP 3 id FROM tblTwo WHERE

         id NOT IN (SELECT TOP 5 id FROM tblTwo WHERE
                                         fieldA = 'value' AND
                                         fieldB = 'value' AND
                                         (fieldC = 'value' OR fieldD =
'value')
                         ORDER BY id) AND
         fieldA = 'value' AND
         fieldB = 'value' AND
         (fieldC = 'value' OR fieldD = 'value')
     ORDER BY id

becomes

SELECT TOP 3 id FROM

      (SELECT TOP 5+3 id FROM tblTwo WHERE
                                         fieldA = 'value' AND
                                         fieldB = 'value' AND
                                         (fieldC = 'value' OR fieldD =
'value')
                         ORDER BY id)