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

Question

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

Tim Biegeleisen · Accepted Answer · 2018-03-07 06:24:34Z

1

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.

answered Mar 7, 2018 at 6:24

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

randy Over a year ago

thanks for the response , i updated the original with more specifics. i will try to incorporate what you have suggested. thanks you

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related