Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2007

    Question Unanswered: list products of the top 5 users with the highest number of products (was "Query")

    Hello, i`m trying since morning to get a result.

    I have a users table and a table which holds products for selling..
    Each user has the option to mark several products as 'special'.

    i need a list which contains the name of the 'special' products which belong to the first 5 users who have the highest number of products.


    select count(user_id),user_name as no
    from users
    natural join products
    order by no desc
    limit 5 -> this query shows the first 5 users which have the most products

    select product_name,user_name
    natural join users
    where product_special='1' -> this query shows all the 'special' products

    I need the result of the two queries combined somehow.


  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    select products.product_name
         , products.user_id
      from users as T
      join products
        on products.user_id = T.user_id
       and products.product_special = 1
     where (
           select count(*) 
             from (
                  select user_id 
                       , count(*) as productcount
                    from products
                      by user_id
                  ) as c
            where productcount > (
                  select count(*) 
                    from products
                   where user_id = T.user_id
           ) < 5 | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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