select top X where X is based on a percentage of records

[WiccaChic]

Hi all. I am trying to build a list that consists of 10% of every machine model I have in a database. I have no idea how to do this, I got started with this:

--
select distinct(model0), count(model0) as counted,
round((count(model0)*.10),0) as test_counts
from v_GS_COMPUTER_SYSTEM
where model0 is not NULL
group by model0
order by count(model0) desc
--

That gets me a list of how many machines equal (or close as possible) to ten percent of the particular model type, but then I want to turn around and select that number of machines from the database. So if I determine I have 800 model A machines so I need to pull a list of 80 of those 800 machines in order to test ten% of my population of that machine type. But I would like to do all that, for every machine in one query or a set of queries.

Is that possible / am I making sense?

 

[r937]

you need to designate some column by which the TOP criterion will be evaluated

in the following example, it's the top 10% of machines based on latest purchase date

TOP makes sense only with ORDER BY

select model0
     , machine_name
     , machine_id
     , machine_purchase_date
  from v_GS_COMPUTER_SYSTEM   as XX
 where machine_purchase_date
    in ( select top 10 percent
                machine_purchase_date
           from v_GS_COMPUTER_SYSTEM
          where model0 = XX.model0
         order by machine_purchase_date desc )

r937.com | rudy.ca