Sql-server – return percentage of records based on value in column

sql-server-2008t-sql

I'm working on a sql query that needs to get a percentage of the records based on the value of a particular column. The percentage is given by the user. A stripped down version of the query is below

SELECT OrganizationID,
       CustomerID,
       OrderID
  FROM x

The output would look something like

 OrganizationID, CustomerID, OrderID 
  123             abc         p456
  123             cba         p457 
  123             bac         p458 
  456             def         p987 
  456             efd         p988

I need to be able to pull say 50 percent of the output for each organizationid. So in this example I'd come back with 2-3 records. Which records doesnt matter as long as the correct percentage of the records is pulled.

Any ideas on how to go about this?

Best Answer

;WITH CTE
     AS (SELECT OrganizationID,
                CustomerID,
                OrderID,
                COUNT(*) OVER (PARTITION BY OrganizationID) AS C,
                ROW_NUMBER() OVER (PARTITION BY OrganizationID 
                                       ORDER BY (SELECT 0))     AS RN
         FROM   YourTable)
SELECT OrganizationID,
       CustomerID,
       OrderID
FROM   CTE
WHERE  RN <= CEILING(0.5 * C)

SQL Fiddle

Related Question