I want to tune a query that gets only the 1st in the ranking-CodePudding

row_number query question.

To get the data where rn is 1, you can wrap the query below and import it with where rn = 1.

Is there a way to get only those whose rn is 1 in the current query??

SELECT a1.member_no 
     , row_number() OVER (PARTITION BY a1.member_no ORDER BY a1.avg_hit_rate desc , a1.top_hit_cnt ) as rn
     , a1.join_no
FROM ht_typing_contents_join_log a1
WHERE a1.reg_date >= STR_TO_DATE(CONCAT( date_format(now(), '%Y%m%d' ) , '000000'), '%Y%m%d%H%i%s')
AND a1.reg_date <= STR_TO_DATE(CONCAT( date_format(now(), '%Y%m%d' ) , '235959'), '%Y%m%d%H%i%s')
and a1.success_yn = 'Y'
AND a1.len_type = '1'

CodePudding user response：

Yes, you should subquery what you currently have and restrict to rn = 1:

WITH cte AS (
    SELECT a1.member_no,
           ROW_NUMBER() OVER (PARTITION BY a1.member_no
                              ORDER BY a1.avg_hit_rate DESC, a1.top_hit_cnt) AS rn,
           a1.join_no
    FROM ht_typing_contents_join_log a1
    WHERE DATE(a1.reg_date) = CURDATE() AND
          a1.success_yn = 'Y' AND
          a1.len_type = '1'
)

SELECT member_no, join_no
FROM cte
WHERE rn = 1;

CodePudding user response：

What I want is below.

SELECT a1. member_no
      , row_number() OVER (PARTITION BY a1.member_no ORDER BY a1.avg_hit_rate desc , a1.top_hit_cnt ) as rn
      , a1. join_no
FROM ht_typing_contents_join_log a1
WHERE a1.reg_date >= STR_TO_DATE(CONCAT( date_format(now(), '%Y%m%d' ) , '000000'), '%Y%m%d%H%i%s')
AND a1.reg_date <= STR_TO_DATE(CONCAT( date_format(now(), '%Y%m%d' ) , '235959'), '%Y%m%d%H%i%s')
and a1.success_yn = 'Y'
AND a1.len_type = '1'
AND rn = 1