Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    12

    Unanswered: Get top 3 records for each ...

    to relate my question to the pubs DB....

    I want to return up to 3 titles for each publisher. The criteria

    So if a publisher only has
    1 title = return 1
    2 titles = return 2
    3 titles = return 3
    4 titles = return only 3
    >4 titles = return only 3

    To make it more interesting, lets return the first 3 alphabetically as well...

    Thanks

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Try this:

    select * from titles a
    where title_id in
    (select top 3 title_id
    from titles b
    where a.pub_id = b.pub_id)
    order by pub_id

  3. #3
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    /*
    JUST A LITTLE ADJUSTED
    */

    select
    case when t.title_id=(
    select min( t3.title_id)
    from titles t3
    where t3.pub_id = t.pub_id and t3.title=(select min(t4.title) from titles t4 where t4.pub_id = t3.pub_id)
    )
    then p.pub_name else '' end
    ,t.title
    from titles t
    join publishers p on t.pub_id=p.pub_id
    where title_id in
    (
    select top 3 t2.title_id
    from titles t2
    where t2.pub_id = t.pub_id
    order by t2.title
    )
    order by p.pub_name,t.title

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •