Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

WiccaChic

Technical User
Joined
Jan 21, 2004
Messages
179
Location
US
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?
 
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
Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top