title: How to rank records within each group and also checking other variable to assign rank if two records had same rank within the group in SQL Server?
body: <p>I have a data set which basically tells you how many coupons were sent for each retailers, and how many responded, their corresponding response rate for each dealer and for each coupons.</p>
  
  <p>I am looking to rank each coupons within each dealer based on response rate and if two coupons had same response rate, then I need to assign better rank to the coupons which was sent the most</p>
  
  <p>This is the script that  I have tried but it is not ranking properly</p>
  
  <pre><code>SELECT 
      DealerCode, Coupon_name, emailsent, responders, responserate,
      RN = RANK() OVER (PARTITION BY DealerCode, Coupon_name, responserate
                        ORDER BY DealerCode, responserate, emailsent)
  FROM 
      table123 
  </code></pre>
  
  <p>This is the expected result</p>
  
  <pre><code>RetailerCode    Coupon_name emailsent   responders  responserate    RN
  ----------------------------------------------------------------------
  A1                Coupon 1     6              1     0.166666667      1
  A1                Coupon 2     10             1     0.1              2
  A1                Coupon 7     50             2     0.04             3
  A1                Coupon 9     25             1     0.04             4
  A2                Coupon 1     28             3     0.10714          2
  A2                Coupon 4     12             0     0                3
  A2                Coupon 3     1217           131   0.1076           1
  A3                Coupon 2     63             10    0.1587           1
  A3                Coupon 6     9              1     0.11111          2
  A3                Coupon 7     3              0     0                3
  A3                Coupon 8     2              0     0                4
  A4                Coupon 4     174            22    0.1266782        3
  A4                Coupon 3     1118           244   0.2182869        1
  A4                Coupon 6     3091           420   0.135877         2
  A5                Coupon 3     1227           78    0.06356962       2
  A5                Coupon 2     780            50    0.064104         1
  A5                Coupon 1     164            6     0.0365866        3
  </code></pre>