Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 6 down vote accepted
;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

share|improve this answer
    
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 Perhaps you should post the issue as a question... –  Paul White Jan 31 '13 at 20:19
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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