Q: Query first five requests for companies using sql I need to list only the first five requests for companies from my database. I have two tables named as Request and Company. There could be any amount of requests for a given company. But, I need to query and list only the first 5 requests of all the companies in my database.
select top 5 *
from Request as r 
    INNER JOIN Company c
    ON r.Company_id=c.Company_ID

I have done the above query but it is returning only the first five from the table not the first five of all members. How can I correct my query? 

A:     With r 
    as
    (select  *, row_number() over(partition by Company_id order by RequestDate) as rown
    from Request as)
    Select *
from r join Company c on c.Company_id=r.Company_id
    Where rown<=5


A: I think you're looking for the top 5 requests for each company that exists in your Company table. In that case, you can rank() each request by company in a subselect and then select where the rank is <= 5:
select company_id, request_id
from
    ( select c.company_id, r.request_id, rank() over (partition by c.company_id order by request_id) as Rank
    from request r
    inner join company c on
        r.company_id = c.company_id
    ) ranked
where rank <= 5


A: You need to ORDER BY the field which determines 'firstness'.  Probably something like:
ORDER BY r.RequestDate

A: ;WITH companyrecords 
     AS (SELECT Company.*, 
                Row_number() OVER (partition BY r.Company_id ORDER BY 
                r.Company_id 
                ASC) 
                   AS 
                RowNumber 
         FROM   Request AS r 
                INNER JOIN Company c 
                  ON r.Company_id = c.Company_ID) 
SELECT * 
FROM   companyrecords 
WHERE  RowNumber <= 5 

