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,
  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?

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

SQL Fiddle

  • I deleted my answer after discovering that it didn't work with (real) tables. I tested locally using table variables, it worked fine with them, but not with tables. – Andriy M Jan 31 '13 at 17:22
  • @AndriyM I still think it's a bug though and that query should work. Wonder if it is taking the count from the whole table not just the rows matching the WHERE. Will try and work it out later! – Martin Smith Jan 31 '13 at 17:28
  • When you do, you'll find that the plans are almost completely identical. They only differ slightly in the non-zero percentages (both table scans' costs are 17% each for table vars versus 16% for tables, and the index spool's cost is 67% vs 68%). – Andriy M Jan 31 '13 at 17:35
  • @AndriyM - From here The top then requests the row count from the spool but the difference is the type of spool. When it works it uses a table spool that is populated separately for each different outer row. When it doesn't work it uses an index spool that is populated once with all the rows then seeked into. It seems to still use the row count from the entire spool however in calculating the percentage. – Martin Smith Feb 1 '13 at 12:31
  • 1
    In my opinion this is a bug and it ought to use the filtered row count in the index spool case not the total row count. I've posted it here connect.microsoft.com/SQLServer/feedback/details/778200/… – Martin Smith Feb 1 '13 at 12:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.