CODEWITHSUNDEEP
sqlmysqlsql-order-bydistinct
Spencer
Spencer

Reputation: 22370

MySQL Distinct Entries disregarding all repeats after ORDER_BY

I want create a query that creates a calculated field ANSWER_SCORE which it then orders the results of the query by. I also want to select two other fields QUESTION_ID and ANSWER_ID.

I only want to select distinct QUESTION_ID's. I also however want to select some other information related to that QUESTION_ID (which will be common to all queries which returned that QUESTION_ID. I am unsure of how to do this.

Here is my query:

SELECT  QUESTION_ID,
        ANSWER_ID,
        sum(ANSWER_SCORE) AS ANSWER_SCORE_SUMMED
FROM(SELECT
        nc.PARENT_COMMUNICATIONS_ID AS QUESTION_ID,
        cr.COMMUNICATIONS_ID AS ANSWER_ID,
        case when  cr.CONSUMER_ID= nc.SENDER_CONSUMER_ID then 2*(1 - EXP(-0.5 * (cal.REAL_TIPS_AMOUNT / ATV.AVG_TIPS) + .15))*(24/(((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(cal.LAST_MOD_TIME)+3600)/3600)))*(IFNULL(ces.EXPERT_SCORE,2) * IFNULL(cirm.CONSUMER_RATING,2) + (12.5 * IFNULL((bit_count((conv(scr_tip.survey_string, 2, 10) & conv(scr_view.survey_string, 2, 10)) | ((0xFFFFFFFF>>(32-6))&~(conv(scr_tip.survey_string, 2, 10)|conv(scr_view.survey_string, 2, 10))))/6),.3)))
            else (1 - EXP(-0.5 * (cal.REAL_TIPS_AMOUNT / ATV.AVG_TIPS) + .15))*(24/(((UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(cal.LAST_MOD_TIME)+3600)/3600)))*(IFNULL(ces.EXPERT_SCORE,2) * IFNULL(cirm.CONSUMER_RATING,2) + (12.5 * IFNULL((bit_count((conv(scr_tip.survey_string, 2, 10) & conv(scr_view.survey_string, 2, 10)) | ((0xFFFFFFFF>>(32-6))&~(conv(scr_tip.survey_string, 2, 10)|conv(scr_view.survey_string, 2, 10))))/6),.3)))
        end as ANSWER_SCORE
  FROM (SELECT 232 AS CONSUMER_ID,
            ACTION_LOG_ID,
            COMMUNICATIONS_ID
     FROM consumer_action_log 
     WHERE COMM_TYPE_ID=4) AS cr
JOIN network_communications AS nc 
    ON cr.COMMUNICATIONS_ID=nc.COMMUNICATIONS_ID
JOIN network_communications AS nc1
    ON nc.PARENT_COMMUNICATIONS_ID=nc1.COMMUNICATIONS_ID
JOIN (SELECT ACTION_LOG_ID,
             LAST_MOD_TIME,
             CONSUMER_ID,
             SENDER_CONSUMER_ID,
             sum(tips_amount) AS real_tips_amount
      FROM consumer_action_log
      WHERE COMM_TYPE_ID=4
      GROUP BY COMMUNICATIONS_ID, SENDER_CONSUMER_ID) AS cal 
ON cr.ACTION_LOG_ID=cal.ACTION_LOG_ID
JOIN communication_interest_mapping AS cim 
    ON nc.PARENT_COMMUNICATIONS_ID=cim.COMMUNICATION_ID
LEFT JOIN consumer_interest_rating_mapping AS cirm 
    ON cr.CONSUMER_ID=cirm.CONSUMER_ID
    AND cim.CONSUMER_INTEREST_EXPERT_ID=cirm.CONSUMER_INTEREST_ID
JOIN network_communications_message AS ncm
  ON nc.PARENT_COMMUNICATIONS_ID=ncm.COMMUNICATIONS_ID
LEFT JOIN consumer_expert_score AS ces 
    ON nc.SENDER_CONSUMER_ID=ces.CONSUMER_ID
    AND cim.CONSUMER_INTEREST_EXPERT_ID=ces.CONSUMER_EXPERT_ID
LEFT JOIN surveycustomerrelation AS scr_tip
     ON cal.SENDER_CONSUMER_ID=scr_tip.Cust_ID
LEFT JOIN surveycustomerrelation AS scr_view
    ON cr.CONSUMER_ID=scr_view.CUST_ID
STRAIGHT_JOIN
    (
        SELECT AVG(cal.TIPS_AMOUNT) AS AVG_TIPS,
                   cal.SENDER_CONSUMER_ID AS CONSUMER_ID
        FROM CONSUMER_ACTION_LOG AS cal
        WHERE COMM_TYPE_ID=4
        GROUP BY cal.SENDER_CONSUMER_ID
    ) AS ATV
    ON cal.SENDER_CONSUMER_ID=ATV.CONSUMER_ID
    ) AS ASM
GROUP BY ANSWER_ID
ORDER BY ANSWER_SCORE_SUMMED DESC
LIMIT 0,20;

Upvotes: 0

Views: 102

Answers (1)

dataduck
dataduck

Reputation: 598

If your other fields really are the same for every QUESTION_ID and ANSWER_ID you return, you can simply add them to your select list and put them in the GROUP BY clause. e.g.

SELECT QUESTION_ID,
    ANSWER_ID,
    FOO,
    BAR,
    SUM(ANSWER_SCORE) AS ANSWER_SCORE_SUMMED
FROM...
<snip>
...GROUP BY QUESTION_ID, ANSWER_ID, FOO, BAR

This has the added benefit of checking your assumption about the other data all being the same for each returned QUESTION_ID, as the different combinations will all be laid out for you with their own ANSWER_SCORE_SUMMED.

Upvotes: 1

Related Questions