TOP in a sub query 
Author Message
 TOP in a sub query

I am trying to accomplish something like this:

select top 10 series_id,  series_name
from series_tbl

order by series_id

That gives me 10 ids that I want to use in a sub query in a different
query.

e.g.,

select series_id, product_id, name, color, price
from product_tbl
where
series_id in (
        select top 10 series_id,  
        from series_tbl

        order by series_id)
order by series_id, product_id

In other words, the whole point is that I only want 10 series at a
time (but the series have multiple product_ids associated with them --
one to many).  So I want all product_ids that are related to the first
10 series.

I'd like to do the above with a join if possible since joins are
generally faster.  

Is it possible to do the above with a join?  I haven't come up with
one that works (the TOP is the problem).  I know you could use a temp
table, but that is even slower.

Thanks,

Jack Gardner



Wed, 24 Apr 2002 03:00:00 GMT
 TOP in a sub query

Hi Jack,

Try this example - this will return multiple titles for the top 10
publishers.

USE pubs
GO

SELECT
    T.title_id,
    T.pub_id
FROM
    titles AS T
WHERE
    T.pub_id IN (
        SELECT
            TOP 10
            P.pub_id
        FROM
            publishers AS P
        ORDER BY
            P.pub_id)
ORDER BY
    T.title_id,
    T.pub_id
GO

Sorry about the font......

--
Best Regards

Trevor Dwyer - SQL Server MVP


Quote:

> I am trying to accomplish something like this:

> select top 10 series_id,  series_name
> from series_tbl

> order by series_id

> That gives me 10 ids that I want to use in a sub query in a different
> query.

> e.g.,

> select series_id, product_id, name, color, price
> from product_tbl
> where
> series_id in (
> select top 10 series_id,
> from series_tbl

> order by series_id)
> order by series_id, product_id

> In other words, the whole point is that I only want 10 series at a
> time (but the series have multiple product_ids associated with them --
> one to many).  So I want all product_ids that are related to the first
> 10 series.

> I'd like to do the above with a join if possible since joins are
> generally faster.

> Is it possible to do the above with a join?  I haven't come up with
> one that works (the TOP is the problem).  I know you could use a temp
> table, but that is even slower.

> Thanks,

> Jack Gardner



Fri, 26 Apr 2002 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. TOP n for sub-query OR iterate thru temp table

2. Query using UDF joined to a sub query vs Temp Table to sub Query

3. SELECT TOP sub query Stored PROC

4. SELECT TOP (SUB QUERY)

5. Sub queries <> sub tablesl

6. Type, sub-type, and sub-sub-type database design

7. Master Parts/Sub Parts/Sub-Sub Parts

8. Update query with sub-query

9. Subject: SQL Query - MAX, Grouping and Sub query

10. Subject: SQL Query - MAX, Grouping and Sub query

11. Tuning a SELECT Query with sub-queries:

12. Query & Sub-Query


 
Powered by phpBB® Forum Software