CODEWITHSUNDEEP
sql
Tug Strongly
Tug Strongly

Reputation: 187

Return 10% of records for each group that meet a certain criteria

I am trying to query a table that has multiple groups to get 10% of the systems in each group that meet a certain criteria.

Here's the data:

create table tablename as
select '4FH6V1' as ComputerName, 'AZ1' as Location, '3093' as Rating from dual union all
select '0GLYQ1' as ComputerName, 'AZ1' as Location, '3093' as Rating from dual union all
select 'P191R1' as ComputerName, 'AZ1' as Location, '3093' as Rating from dual union all
select '7CMJ02' as ComputerName, 'AZ3' as Location, '3392' as Rating from dual union all
select '8W2QS1' as ComputerName, 'AZ4' as Location, '3093' as Rating from dual union all
select 'K9CHX1' as ComputerName, 'AZ7' as Location, '3192' as Rating from dual union all
select '3XZNS1' as ComputerName, 'AZ7' as Location, '3093' as Rating from dual union all
select '79RGX1' as ComputerName, 'AZ9' as Location, '3192' as Rating from dual union all
select '02BR22' as ComputerName, 'AZ3' as Location, '2593' as Rating from dual
;

| ComputerName | Location | Rating |
|--------------|----------|--------|
| 4FH6V1       | AZ1      | 3093   |
| 0GLYQ1       | AZ1      | 3093   |
| P191R1       | AZ1      | 3093   |
| 7CMJ02       | AZ3      | 3392   |
| 8W2QS1       | AZ4      | 3093   |
| K9CHX1       | AZ7      | 3192   |
| 3XZNS1       | AZ7      | 3093   |
| 79RGX1       | AZ9      | 3192   |
| 02BR22       | AZ3      | 2593   |

There are many more records like this in the table. For example, I need to find 10% of the Computer Names in each Location that have the Rating of 3093.

Upvotes: 0

Views: 47

Answers (3)

Josh Gilfillan
Josh Gilfillan

Reputation: 5146

This should work on Oracle, although the sample data is not large enough to get a 10% sample...

create table tablename as
select '4FH6V1' as ComputerName, 'AZ1' as Location, '3093' as Rating from dual union all
select '0GLYQ1' as ComputerName, 'AZ1' as Location, '3093' as Rating from dual union all
select 'P191R1' as ComputerName, 'AZ1' as Location, '3093' as Rating from dual union all
select '7CMJ02' as ComputerName, 'AZ3' as Location, '3392' as Rating from dual union all
select '8W2QS1' as ComputerName, 'AZ4' as Location, '3093' as Rating from dual union all
select 'K9CHX1' as ComputerName, 'AZ7' as Location, '3192' as Rating from dual union all
select '3XZNS1' as ComputerName, 'AZ7' as Location, '3093' as Rating from dual union all
select '79RGX1' as ComputerName, 'AZ9' as Location, '3192' as Rating from dual union all
select '02BR22' as ComputerName, 'AZ3' as Location, '2593' as Rating from dual
;

with cte1 as (
select
   x.ComputerName
  ,x.Location
  ,x.Rating
  ,count(1) over (partition by Location) as location_total
  ,row_number() over (partition by Location order by ComputerName) as location_position
  ,row_number() over (partition by Location order by ComputerName) / count(1) over (partition by Location) as location_pct
from tablename x
where x.Rating = 3093
)
select *
from cte1
where location_pct <= 0.1

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269873

It you are using SQL Server, you can use apply:

select tt.*
from (select distinct location from t where t.rating = 3093) l apply
     (select top (10) PERCENT t.*
      from t
      where t.location = l.location and t.rating = 3093
     ) tt

Upvotes: 0

milton
milton

Reputation: 57

If you are using SQL server you can use the PERCENT in your select statement:

SELECT TOP 10 PERCENT * FROM tablename WHERE RATING=3093;

Upvotes: 2

Related Questions