Question 21296 in Mysql Question posted in
The official MySQL documentation can be found here.

I want to tune a query that gets only the 1st in the ranking – Mysql

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'
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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

  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search