Set query order by comparison result of two fields

  • halifaxdal

    SSCoach

    Points: 19741

    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.

  • Phil Parkin

    SSC Guru

    Points: 244589

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • drew.allen

    SSC Guru

    Points: 76737

    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

  • halifaxdal

    SSCoach

    Points: 19741

    drew.allen - Tuesday, February 28, 2017 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

    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

  • Jeff Atherton

    SSCrazy

    Points: 2663


    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

  • halifaxdal

    SSCoach

    Points: 19741

    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.

  • Jeff Atherton

    SSCrazy

    Points: 2663

    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 ...

  • drew.allen

    SSC Guru

    Points: 76737

    halifaxdal - Thursday, March 2, 2017 12:44 PM

    drew.allen - Tuesday, February 28, 2017 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

    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

  • halifaxdal

    SSCoach

    Points: 19741

    drew.allen - Thursday, March 2, 2017 3:07 PM

    halifaxdal - Thursday, March 2, 2017 12:44 PM

    drew.allen - Tuesday, February 28, 2017 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

    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

    Yes, thank you Drew, my bad.

  • halifaxdal

    SSCoach

    Points: 19741

    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.

  • drew.allen

    SSC Guru

    Points: 76737

    halifaxdal - Thursday, March 2, 2017 4:53 PM

    drew.allen - Thursday, March 2, 2017 3:07 PM

    This 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 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)?

    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

  • halifaxdal

    SSCoach

    Points: 19741

    drew.allen - Monday, March 6, 2017 9:05 AM

    You 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

  • drew.allen

    SSC Guru

    Points: 76737

    halifaxdal - Monday, March 6, 2017 9:31 AM

    drew.allen - Monday, March 6, 2017 9:05 AM

    You 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

  • halifaxdal

    SSCoach

    Points: 19741

    drew.allen - Monday, March 6, 2017 12:26 PM

    halifaxdal - Monday, March 6, 2017 9:31 AM

    drew.allen - Monday, March 6, 2017 9:05 AM

    You 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

    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 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply