CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
randy
randy

Reputation: 1887

Postgres Query with count and would like to get the top row

i have this query, which works fine

SELECT count(*) cnt, b.message_lot_num, 
       t.name AS trigger_name, tm.name  AS template_name 
FROM messages b 
 LEFT JOIN triggers t ON t.trigger_id = b.trigger_id 
 LEFT JOIN templates tm ON  tm.template_id = t.template_id 
WHERE b.account_id = $1 
  AND sms_direction = 'out' 
GROUP BY message_lot_num, t.name, tm.name
ORDER BY message_lot_num DESC LIMIT $2

What i would like to do is return the top record for each group by.

each of these groups message_lot_num shares the same message(but is just a little different because of the recpt name, so i can not add that in the group by). So i am trying to get a sample message to display with that group

is that possible?

thanks for any help

Upvotes: 0

Views: 35

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522752

You want to get the top record with regards to which column or quantity? It doesn't make sense to even ask this without knowing how to order each group. I will assume that you want the record with the highest count:

WITH cte AS (
    SELECT
        COUNT(*) cnt,
        b.message_lot_num,
        t.name AS trigger_name
        tm.name AS template_name,
        ROW_NUMBER() OVER (PARTITION BY message_lot_num, t.name, tm.name
            ORDER BY COUNT(*) DESC) rn    -- you may order by any column here
    FROM messages b
    LEFT JOIN triggers t
        ON t.trigger_id = b.trigger_id
    LEFT JOIN templates tm
        ON tm.template_id = t.template_id
    WHERE
        b.account_id = $1 AND
        sms_direction = 'out' 
    GROUP BY
        message_lot_num, t.name, tm.name
)

SELECT
    cnt, message_lot_num, trigger_name, template_name
FROM cte
WHERE rn = 1
ORDER BY
    message_lot_num DESC;

As mentioned above in the comment, you may order by any column in the call to ROW_NUMBER you wish. I chose the count because that seemed to make the most sense.

Upvotes: 1

Related Questions