Find 95th percentile
-
I need to find patients(MRN) who are in the 95th percentile for BMI. Any thoughts?
create table dbo.TEST
(
MRN varchar(10),
ResultValue varchar(10)
)
insert into dbo.TEST(MRN, BMI) values( '611193','25.63')
insert into dbo.TEST(MRN, BMI) values( '128845','16.93')
insert into dbo.TEST(MRN, BMI) values( '128848','20.91')
insert into dbo.TEST(MRN, BMI) values( '128875','20.78')
insert into dbo.TEST(MRN, BMI) values( '229759','19.32')
insert into dbo.TEST(MRN, BMI) values( '229786','30.44')
insert into dbo.TEST(MRN, BMI) values( '229928','18.88')
insert into dbo.TEST(MRN, BMI) values( '239395','24.66')
insert into dbo.TEST(MRN, BMI) values( '270108','20.2')
insert into dbo.TEST(MRN, BMI) values( '273802','25.24')
insert into dbo.TEST(MRN, BMI) values( '276170','23.08')
insert into dbo.TEST(MRN, BMI) values( '279601','34.46')
insert into dbo.TEST(MRN, BMI) values( '281469','17.68')
insert into dbo.TEST(MRN, BMI) values( '281907','21.5')
insert into dbo.TEST(MRN, BMI) values( '300072','22.07')
insert into dbo.TEST(MRN, BMI) values( '300723','25.88')
insert into dbo.TEST(MRN, BMI) values( '300807','25.21')
insert into dbo.TEST(MRN, BMI) values( '300927','23.09')
insert into dbo.TEST(MRN, BMI) values( '301124','24.39')
insert into dbo.TEST(MRN, BMI) values( '302130','26.81')
insert into dbo.TEST(MRN, BMI) values( '302485','20.94')
insert into dbo.TEST(MRN, BMI) values( '302578','33.15')
insert into dbo.TEST(MRN, BMI) values( '303309','34.57')
insert into dbo.TEST(MRN, BMI) values( '303496','22.3')
insert into dbo.TEST(MRN, BMI) values( '303752','23.59')
insert into dbo.TEST(MRN, BMI) values( '303998','17.77')
insert into dbo.TEST(MRN, BMI) values( '304282','19.4')
insert into dbo.TEST(MRN, BMI) values( '307450','29.76')
insert into dbo.TEST(MRN, BMI) values( '308127','40.03')
insert into dbo.TEST(MRN, BMI) values( '308243','19.8')
insert into dbo.TEST(MRN, BMI) values( '308488','21.47')
insert into dbo.TEST(MRN, BMI) values( '308583','16.79')
insert into dbo.TEST(MRN, BMI) values( '308696','19.82')
insert into dbo.TEST(MRN, BMI) values( '308802','21.28')
insert into dbo.TEST(MRN, BMI) values( '308841','20.63')
insert into dbo.TEST(MRN, BMI) values( '309657','20.65')
insert into dbo.TEST(MRN, BMI) values( '310067','17.94')
insert into dbo.TEST(MRN, BMI) values( '310144','20.24')
insert into dbo.TEST(MRN, BMI) values( '320138','23.49')
insert into dbo.TEST(MRN, BMI) values( '320144','22.02')
insert into dbo.TEST(MRN, BMI) values( '320152','17.31')
insert into dbo.TEST(MRN, BMI) values( '321704','15.55')
insert into dbo.TEST(MRN, BMI) values( '321835','15.95')
insert into dbo.TEST(MRN, BMI) values( '321916','16.06')
insert into dbo.TEST(MRN, BMI) values( '323063','23.12')
insert into dbo.TEST(MRN, BMI) values( '323200','19.14')
insert into dbo.TEST(MRN, BMI) values( '323212','13.52')
insert into dbo.TEST(MRN, BMI) values( '323872','16.69')
insert into dbo.TEST(MRN, BMI) values( '323920','22.31')
insert into dbo.TEST(MRN, BMI) values( '323958','16.86')
insert into dbo.TEST(MRN, BMI) values( '324795','19.2')
insert into dbo.TEST(MRN, BMI) values( '325174','16.02')
insert into dbo.TEST(MRN, BMI) values( '325441','16.48')
insert into dbo.TEST(MRN, BMI) values( '325464','18.58')
insert into dbo.TEST(MRN, BMI) values( '325480','15.13')
insert into dbo.TEST(MRN, BMI) values( '325657','19.26')
insert into dbo.TEST(MRN, BMI) values( '325695','11')
insert into dbo.TEST(MRN, BMI) values( '325944','17.98')
insert into dbo.TEST(MRN, BMI) values( '326070','17.12')
insert into dbo.TEST(MRN, BMI) values( '326249','19.9')
insert into dbo.TEST(MRN, BMI) values( '326329','26.48')
insert into dbo.TEST(MRN, BMI) values( '326487','13.87')
insert into dbo.TEST(MRN, BMI) values( '326774','31.81')
insert into dbo.TEST(MRN, BMI) values( '326926','20.52')
insert into dbo.TEST(MRN, BMI) values( '326949','16.94')
insert into dbo.TEST(MRN, BMI) values( '327118','30.07')
insert into dbo.TEST(MRN, BMI) values( '327468','15.62')
insert into dbo.TEST(MRN, BMI) values( '328479','15.73')
insert into dbo.TEST(MRN, BMI) values( '328633','14.96')
insert into dbo.TEST(MRN, BMI) values( '328701','25.42')
insert into dbo.TEST(MRN, BMI) values( '329004','21.57')
insert into dbo.TEST(MRN, BMI) values( '329067','17.15')
insert into dbo.TEST(MRN, BMI) values( '329112','16.43')
insert into dbo.TEST(MRN, BMI) values( '329142','15.97')
insert into dbo.TEST(MRN, BMI) values( '329267','19.69')
insert into dbo.TEST(MRN, BMI) values( '329321','17.13')
insert into dbo.TEST(MRN, BMI) values( '329453','16.26')
insert into dbo.TEST(MRN, BMI) values( '329472','17.65')
insert into dbo.TEST(MRN, BMI) values( '330121','14.64')
insert into dbo.TEST(MRN, BMI) values( '330232','20.96')
insert into dbo.TEST(MRN, BMI) values( '330383','16.14')
insert into dbo.TEST(MRN, BMI) values( '330392','16.84')
insert into dbo.TEST(MRN, BMI) values( '330421','16.66')
insert into dbo.TEST(MRN, BMI) values( '330551','21.93')
insert into dbo.TEST(MRN, BMI) values( '330879','19.53')
insert into dbo.TEST(MRN, BMI) values( '331253','14.49')
insert into dbo.TEST(MRN, BMI) values( '331276','15.99')
insert into dbo.TEST(MRN, BMI) values( '331974','15.89')
insert into dbo.TEST(MRN, BMI) values( '333281','15.51')
insert into dbo.TEST(MRN, BMI) values( '334579','21.78')
insert into dbo.TEST(MRN, BMI) values( '334770','14.89')
insert into dbo.TEST(MRN, BMI) values( '335133','18.86')
insert into dbo.TEST(MRN, BMI) values( '335329','16.34')
insert into dbo.TEST(MRN, BMI) values( '335409','17.25')
insert into dbo.TEST(MRN, BMI) values( '335418','21.32')
insert into dbo.TEST(MRN, BMI) values( '336176','15.94')
insert into dbo.TEST(MRN, BMI) values( '336320','19.16')
insert into dbo.TEST(MRN, BMI) values( '336323','13.29')
insert into dbo.TEST(MRN, BMI) values( '336329','15.84')
-
to find the percentile, you need the age, gender and BMI. then you could use a lookup table.
Body mass index-for-age percentiles: Boys, 2 to 20 years
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I need the 95th percentile of my population.
-
Your test data script doesn't work. But anyway, have you tried the NTILE function?
John
-
My bad. Replace ResultValue with BMI.
I've read mixed reviews on NTILE.
-
Maybe I'm oversimplifying it, but do you mean you want the top 5 percent of rows with the highest BMI?
-
Yes.
-
One approach would be a simple sort to return the top 5 percent:
SELECT TOP 5 PERCENT mrn, bmiFROM dbo.test
ORDER BY BMI DESC;
Another approach would be to use the NTILE function. I've found it to work well.
WITH cte AS (SELECT mrn, bmi, NTILE(100) OVER(ORDER BY bmi DESC) percentile
FROM dbo.test
)
SELECT MRN, BMI, percentile
FROM cte
WHERE percentile <= 5
ORDER BY percentile;
-
Ed Wagner (3/4/2015)
One approach would be a simple sort to return the top 5 percent:SELECT TOP 5 PERCENT mrn, bmiFROM dbo.test
ORDER BY BMI DESC;
Another approach would be to use the NTILE function. I've found it to work well.
WITH cte AS (SELECT mrn, bmi, NTILE(100) OVER(ORDER BY bmi DESC) percentile
FROM dbo.test
)
SELECT MRN, BMI, percentile
FROM cte
WHERE percentile <= 5
ORDER BY percentile;
Edit: Come to think of it, since this is a simple sort, you could also use the ROW_NUMBER window function to do the same thing instead of NTILE. Whatever approach you decide on, make sure to test it on a larger data set than 99 rows. You want to make sure it performs well.
-
Thanx.
-
NineIron (3/4/2015)
Thanx.No problem. Glad to help.
-
One small-ish tidbit: you want to be VERY clear about what is expected when the sample set doesn't evenly divide into the chunks you've decided on. In your case 99 doesn't divide evenly into 100 parts, so one of the NTILE groupings will have fewer 1 members than others (the smaller ones will be "to the end").
Example:
--in this case the NTILE grouping has 5 members
with nteCTE1 as (
select ntile(20) over (order by BMI desc) NT, * from dbo.test)
select * from ntecte1 where nt=1;
--in this case the NTILE grouping has 4 members
with nteCTE2 as (
select ntile(20) over (order by BMI) NT, * from dbo.test)
select * from ntecte2 where nt=20
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply