Scarlett72
asked on
Select bottom 25%
I am trying to find the bottom 25% value of the 'Value' column, broken up by MainGroup ideally broken into 4 quarters.
Any help would be appreciated.
IF OBJECT_ID('TempDB..#mytabl e','U') IS NOT NULL
DROP TABLE #mytable
CREATE TABLE #mytable
(
MainGroup varchar(50),
SubGroup varchar(50),
Value int
)
insert into #mytable
(MainGroup, SubGroup, Value)
select 'Temporary Assignments','Internal','7 83' union all
select 'Temporary Assignments','Internal','2 25' union all
select 'Temporary Assignments','Internal','1 18' union all
select 'Temporary Assignments','Internal','1 014' union all
select 'Operation''s Support','Internal','2006' union all
select 'Operation''s Support','Internal','6352' union all
select 'Operation''s Support','Internal','186' union all
select 'Operation''s Support','Internal','186' union all
select 'Technical','external','18 6' union all
select 'Technical','external','36 7' union all
select 'Technical','external','14 2' union all
select 'Technical','external','46 ' union all
select 'Loyalty','external','5600 ' union all
select 'Loyalty','external','194' union all
select 'Loyalty','external','3679 ' union all
select 'Loyalty','external','48' union all
select 'Loyalty','external','5520 ' union all
select 'Technical','external','60 ' union all
select 'Technical','external','14 0' union all
select 'Loyalty','external','1287 ' union all
select 'Technical','external','26 1' union all
select 'Loyalty','external','5533 ' ;
with cte as
(
select *,
ROW_NUMBER() over(PARTITION by MainGroup order by value desc) as 'rn'
from #mytable
)
select * from cte
--the results would look like this ...
Loyalty external 1287 5
Loyalty external 194 6
Loyalty external 48 7
Operation's Support Internal 186 4
Technical external 140 5
Technical external 60 6
Technical external 46 7
Temporary Assignments Internal 118 4
Any help would be appreciated.
IF OBJECT_ID('TempDB..#mytabl
DROP TABLE #mytable
CREATE TABLE #mytable
(
MainGroup varchar(50),
SubGroup varchar(50),
Value int
)
insert into #mytable
(MainGroup, SubGroup, Value)
select 'Temporary Assignments','Internal','7
select 'Temporary Assignments','Internal','2
select 'Temporary Assignments','Internal','1
select 'Temporary Assignments','Internal','1
select 'Operation''s Support','Internal','2006'
select 'Operation''s Support','Internal','6352'
select 'Operation''s Support','Internal','186' union all
select 'Operation''s Support','Internal','186' union all
select 'Technical','external','18
select 'Technical','external','36
select 'Technical','external','14
select 'Technical','external','46
select 'Loyalty','external','5600
select 'Loyalty','external','194'
select 'Loyalty','external','3679
select 'Loyalty','external','48' union all
select 'Loyalty','external','5520
select 'Technical','external','60
select 'Technical','external','14
select 'Loyalty','external','1287
select 'Technical','external','26
select 'Loyalty','external','5533
with cte as
(
select *,
ROW_NUMBER() over(PARTITION by MainGroup order by value desc) as 'rn'
from #mytable
)
select * from cte
--the results would look like this ...
Loyalty external 1287 5
Loyalty external 194 6
Loyalty external 48 7
Operation's Support Internal 186 4
Technical external 140 5
Technical external 60 6
Technical external 46 7
Temporary Assignments Internal 118 4
You can order ASC and Select Top 25%
ASKER
Hi eghtebas,
this gives me the bottom 25% of row_numbers, I need the bottom 25% based on the Value by the Main group.
this gives me the bottom 25% of row_numbers, I need the bottom 25% based on the Value by the Main group.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Please note that my last post has been revised.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.