February 28, 2017 at 2:59 pm
I have a dataset like below:
CREATE TABLE [dbo].[tmpRate](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CancerType] [varchar](50) NULL,
[Sex] [varchar](20) NULL,
[Rate] [float] NULL
) ON [PRIMARY]
Some sample data are like below:
Insert into tmpRate Values (135,'Acute lymphocytic leukemia','Females' ,0.23)
Insert into tmpRate Values (136,'Acute lymphocytic leukemia','Males' ,0.11)
Insert into tmpRate Values (138,'Acute myeloid leukemia','Females',0.34)
Insert into tmpRate Values (139,'Acute myeloid leukemia','Males',0.13)
Insert into tmpRate Values (141,'Anus','Females',0.44)
Insert into tmpRate Values (142,'Anus','Males',0.55)
Insert into tmpRate Values (144,'Bladder (including in situ)','Females',0.76)
Insert into tmpRate Values (145,'Bladder (including in situ)','Males',0.77)
Insert into tmpRate Values (147,'Bones and joints (20 to 99 years)','Females',0.18)
Insert into tmpRate Values (148,'Bones and joints (20 to 99 years)','Males',0.53)
What I need to get is the result sorted in the order of the last field Rate:
If the Rate for Male is higher than Female to same CancerType, then take Male's Rate, otherwise takes Female's Rate
Thank you very much, any suggestion is welcome and appreciated.
February 28, 2017 at 3:16 pm
I don't understand this bit
If the Rate for Male is higher than Female to same CancerType, then take Male's Rate, otherwise takes Female's Rate
Can you show us what the results would look like, based on your sample data?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 28, 2017 at 3:18 pm
SELECT *
FROM
(
Values (135,'Acute lymphocytic leukemia','Females' ,0.23)
, (136,'Acute lymphocytic leukemia','Males' ,0.11)
, (138,'Acute myeloid leukemia','Females',0.34)
, (139,'Acute myeloid leukemia','Males',0.13)
, (141,'Anus','Females',0.44)
, (142,'Anus','Males',0.55)
, (144,'Bladder (including in situ)','Females',0.76)
, (145,'Bladder (including in situ)','Males',0.77)
, (147,'Bones and joints (20 to 99 years)','Females',0.18)
, (148,'Bones and joints (20 to 99 years)','Males',0.53)
) tmpRate(ID, CancerType, Sex, Rate)
ORDER BY MAX(Rate) OVER(PARTITION BY CancerType) DESC, CancerType, Rate DESC, Sex
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 2, 2017 at 12:44 pm
drew.allen - Tuesday, February 28, 2017 3:18 PMSELECT *
FROM
(
Values (135,'Acute lymphocytic leukemia','Females' ,0.23)
, (136,'Acute lymphocytic leukemia','Males' ,0.11)
, (138,'Acute myeloid leukemia','Females',0.34)
, (139,'Acute myeloid leukemia','Males',0.13)
, (141,'Anus','Females',0.44)
, (142,'Anus','Males',0.55)
, (144,'Bladder (including in situ)','Females',0.76)
, (145,'Bladder (including in situ)','Males',0.77)
, (147,'Bones and joints (20 to 99 years)','Females',0.18)
, (148,'Bones and joints (20 to 99 years)','Males',0.53)
) tmpRate(ID, CancerType, Sex, Rate)
ORDER BY MAX(Rate) OVER(PARTITION BY CancerType) DESC, CancerType, Rate DESC, SexDrew
Thank you Drew,
The result is not what is expecting:
for each CancerType, only the higher one will be included in the final result, so the result should look like the following:
1 Bladder (including in situ) Males 0.77
2 Anus Males 0.55
3 Bones and joints (20 to 99 years) Males 0.53
4 Acute myeloid leukemia Females 0.34
5 Acute lymphocytic leukemia Females 0.23
March 2, 2017 at 1:13 pm
With CTE AS
(
SELECT
ID
,CancerType
,Sex
,Rate
,ROW_NUMBER() OVER(PARTITION BY CancerType Order by Rate desc) as 'Row'
FROM tmpRate
)
SELECT ID, CancerType, Sex, Rate
FROM CTE
WHERE Row = 1
ORDER BY Rate desc
March 2, 2017 at 1:25 pm
Jeff Atherton - Thursday, March 2, 2017 1:13 PM
With CTE AS
(
SELECT
ID
,CancerType
,Sex
,Rate
,ROW_NUMBER() OVER(PARTITION BY CancerType Order by Rate desc) as 'Row'
FROM tmpRate
)
SELECT ID, CancerType, Sex, Rate
FROM CTE
WHERE Row = 1
ORDER BY Rate desc
Thank you very much, it gets me the needed result.
March 2, 2017 at 1:36 pm
You're welcome. If you haven't worked with Common Table Expressions that much just note that any statement that comes before it will need to end with a semicolon. Some people will just start their CTE with it...
;WITH CTE AS ...
March 2, 2017 at 3:07 pm
halifaxdal - Thursday, March 2, 2017 12:44 PMdrew.allen - Tuesday, February 28, 2017 3:18 PMSELECT *
FROM
(
Values (135,'Acute lymphocytic leukemia','Females' ,0.23)
, (136,'Acute lymphocytic leukemia','Males' ,0.11)
, (138,'Acute myeloid leukemia','Females',0.34)
, (139,'Acute myeloid leukemia','Males',0.13)
, (141,'Anus','Females',0.44)
, (142,'Anus','Males',0.55)
, (144,'Bladder (including in situ)','Females',0.76)
, (145,'Bladder (including in situ)','Males',0.77)
, (147,'Bones and joints (20 to 99 years)','Females',0.18)
, (148,'Bones and joints (20 to 99 years)','Males',0.53)
) tmpRate(ID, CancerType, Sex, Rate)
ORDER BY MAX(Rate) OVER(PARTITION BY CancerType) DESC, CancerType, Rate DESC, SexDrew
Thank you Drew,
The result is not what is expecting:
for each CancerType, only the higher one will be included in the final result, so the result should look like the following:
1 Bladder (including in situ) Males 0.77
2 Anus Males 0.55
3 Bones and joints (20 to 99 years) Males 0.53
4 Acute myeloid leukemia Females 0.34
5 Acute lymphocytic leukemia Females 0.23
This is why we ask for EXPECTED RESULTS as well as sample data.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 2, 2017 at 4:53 pm
drew.allen - Thursday, March 2, 2017 3:07 PMhalifaxdal - Thursday, March 2, 2017 12:44 PMdrew.allen - Tuesday, February 28, 2017 3:18 PMSELECT *
FROM
(
Values (135,'Acute lymphocytic leukemia','Females' ,0.23)
, (136,'Acute lymphocytic leukemia','Males' ,0.11)
, (138,'Acute myeloid leukemia','Females',0.34)
, (139,'Acute myeloid leukemia','Males',0.13)
, (141,'Anus','Females',0.44)
, (142,'Anus','Males',0.55)
, (144,'Bladder (including in situ)','Females',0.76)
, (145,'Bladder (including in situ)','Males',0.77)
, (147,'Bones and joints (20 to 99 years)','Females',0.18)
, (148,'Bones and joints (20 to 99 years)','Males',0.53)
) tmpRate(ID, CancerType, Sex, Rate)
ORDER BY MAX(Rate) OVER(PARTITION BY CancerType) DESC, CancerType, Rate DESC, SexDrew
Thank you Drew,
The result is not what is expecting:
for each CancerType, only the higher one will be included in the final result, so the result should look like the following:
1 Bladder (including in situ) Males 0.77
2 Anus Males 0.55
3 Bones and joints (20 to 99 years) Males 0.53
4 Acute myeloid leukemia Females 0.34
5 Acute lymphocytic leukemia Females 0.23This is why we ask for EXPECTED RESULTS as well as sample data.
Drew
Yes, thank you Drew, my bad.
March 6, 2017 at 7:51 am
I am to generate the result using the adapted script below:
Create Proc spListSurvivalRate
As
Set rowcount 0;
With CTE AS
(
SELECT
ID
,CancerType
,Sex
,Rate
,ROW_NUMBER() OVER(PARTITION BY CancerType Order by Rate desc) as 'Row'
FROM tmpRate
)
SELECT ROW_NUMBER() OVER(ORDER BY CancerType, Rate desc, Sex) as ID, CancerType, Sex, Rate
FROM CTE
Where Sex <> 'Both sexes'
ORDER BY CancerType, Rate desc, Sex
With the understand of certain CancerType might apply to only one Sex, how can I modify the script so that the result always show result for both Sex (show 0 if not applicable)?
The final result would look like below (order by Cancer Type, Sex)
1 Anus Males 0.55
2 Anus Females 0
3 Bones and joints (20 to 99 years) Males 0
4 Bones and joints (20 to 99 years) Females 0.53
5 Acute myeloid leukemia Males 0.34
6 Acute myeloid leukemia Females 0.23
I hope it is clear.
Thank you.
March 6, 2017 at 9:06 am
halifaxdal - Thursday, March 2, 2017 4:53 PMdrew.allen - Thursday, March 2, 2017 3:07 PMThis is why we ask for EXPECTED RESULTS as well as sample data.Drew
Yes, thank you Drew, my bad.
halifaxdal - Monday, March 6, 2017 7:51 AMI am to generate the result using the adapted script below:
Create Proc spListSurvivalRate
As
Set rowcount 0;
With CTE AS
(
SELECT
ID
,CancerType
,Sex
,Rate
,ROW_NUMBER() OVER(PARTITION BY CancerType Order by Rate desc) as 'Row'
FROM tmpRate
)
SELECT ROW_NUMBER() OVER(ORDER BY CancerType, Rate desc, Sex) as ID, CancerType, Sex, Rate
FROM CTE
Where Sex <> 'Both sexes'
ORDER BY CancerType, Rate desc, SexWith the understand of certain CancerType might apply to only one Sex, how can I modify the script so that the result always show result for both Sex (show 0 if not applicable)?
Thank you.
You need to update your data to include records for 'Both sexes' and then update your expected results.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 6, 2017 at 9:31 am
drew.allen - Monday, March 6, 2017 9:05 AMYou need to update your data to include records for 'Both sexes' and then update your expected results.Drew
Thank Drew.
The new data is like :
Insert into tmpRate Values (136,'Acute lymphocytic leukemia','Males' ,0.11)
Insert into tmpRate Values (138,'Acute myeloid leukemia','Females',0.34)
Insert into tmpRate Values (139,'Acute myeloid leukemia','Males',0.13)
Insert into tmpRate Values (141,'Anus','Females',0.44)
Insert into tmpRate Values (142,'Anus','Males',0.55)
Insert into tmpRate Values (144,'Bladder (including in situ)','Females',0.76)
Insert into tmpRate Values (145,'Bladder (including in situ)','Males',0.77)
Insert into tmpRate Values (147,'Bones and joints (20 to 99 years)','Females',0.18)
The expected output:
1,'Acute lymphocytic leukemia','Males' ,0.11
2,'Acute lymphocytic leukemia','Females' ,0
3,'Acute myeloid leukemia','Females',0.34
4,'Acute myeloid leukemia','Males',0.13
5,'Anus','Males',0.55
6,'Anus','Females',0.44
7,'Bladder (including in situ)','Males',0.77
8,'Bladder (including in situ)','Females',0.76
9,'Bones and joints (20 to 99 years)','Females',0.18
10,'Bones and joints (20 to 99 years)','Males',0
March 6, 2017 at 12:26 pm
halifaxdal - Monday, March 6, 2017 9:31 AMdrew.allen - Monday, March 6, 2017 9:05 AMYou need to update your data to include records for 'Both sexes' and then update your expected results.Drew
Thank Drew.
The new data is like :
Insert into tmpRate Values (136,'Acute lymphocytic leukemia','Males' ,0.11)
Insert into tmpRate Values (138,'Acute myeloid leukemia','Females',0.34)
Insert into tmpRate Values (139,'Acute myeloid leukemia','Males',0.13)
Insert into tmpRate Values (141,'Anus','Females',0.44)
Insert into tmpRate Values (142,'Anus','Males',0.55)
Insert into tmpRate Values (144,'Bladder (including in situ)','Females',0.76)
Insert into tmpRate Values (145,'Bladder (including in situ)','Males',0.77)
Insert into tmpRate Values (147,'Bones and joints (20 to 99 years)','Females',0.18)The expected output:
1,'Acute lymphocytic leukemia','Males' ,0.11
2,'Acute lymphocytic leukemia','Females' ,0
3,'Acute myeloid leukemia','Females',0.34
4,'Acute myeloid leukemia','Males',0.13
5,'Anus','Males',0.55
6,'Anus','Females',0.44
7,'Bladder (including in situ)','Males',0.77
8,'Bladder (including in situ)','Females',0.76
9,'Bones and joints (20 to 99 years)','Females',0.18
10,'Bones and joints (20 to 99 years)','Males',0
These results do not match your previous requirements. Specifically, they are not sorted in the correct order and they include records for both sexes when you previously stated that you only wanted the record with the highest percentage.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 6, 2017 at 1:25 pm
drew.allen - Monday, March 6, 2017 12:26 PMhalifaxdal - Monday, March 6, 2017 9:31 AMdrew.allen - Monday, March 6, 2017 9:05 AMYou need to update your data to include records for 'Both sexes' and then update your expected results.Drew
Thank Drew.
The new data is like :
Insert into tmpRate Values (136,'Acute lymphocytic leukemia','Males' ,0.11)
Insert into tmpRate Values (138,'Acute myeloid leukemia','Females',0.34)
Insert into tmpRate Values (139,'Acute myeloid leukemia','Males',0.13)
Insert into tmpRate Values (141,'Anus','Females',0.44)
Insert into tmpRate Values (142,'Anus','Males',0.55)
Insert into tmpRate Values (144,'Bladder (including in situ)','Females',0.76)
Insert into tmpRate Values (145,'Bladder (including in situ)','Males',0.77)
Insert into tmpRate Values (147,'Bones and joints (20 to 99 years)','Females',0.18)The expected output:
1,'Acute lymphocytic leukemia','Males' ,0.11
2,'Acute lymphocytic leukemia','Females' ,0
3,'Acute myeloid leukemia','Females',0.34
4,'Acute myeloid leukemia','Males',0.13
5,'Anus','Males',0.55
6,'Anus','Females',0.44
7,'Bladder (including in situ)','Males',0.77
8,'Bladder (including in situ)','Females',0.76
9,'Bones and joints (20 to 99 years)','Females',0.18
10,'Bones and joints (20 to 99 years)','Males',0These results do not match your previous requirements. Specifically, they are not sorted in the correct order and they include records for both sexes when you previously stated that you only wanted the record with the highest percentage.
Drew
Thank you Drew for pointing out that, I've posted it as a new question here:
https://www.sqlservercentral.com/Forums/Topic1862990.aspx?Update=1
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply