Postgresql – Have both distinct and count

postgresql

I have a table users(id and name) and order(cost and user: FK to users).

users.name users.id
bob               1
alice             1

and

orders.user orders.cost
          1          15
          1           5
          2           4
          1           4
          1           3
          1           3
          2           3

Here is a query which joints users and orders and sorts by order cost.

users.name users.id orders.user orders.cost
bob               1           1          15
alice             1           1           5
bob               2           2           4
alice             1           1           4
alice             1           1           3
alice             1           1           3
bob               2           2           3

If I were to do a distinct on user (in Postgresql), I would get

users.name    cost
bob              15
alice             5

I actually want the counts, while still being sorted on highest order cost/

users.name    count
bob               3
alice             4

Best Answer

Not sure I understand, but I guess something like:

select u.name, count(*)
from users u
join order o
    on u.id = o.user
group by u.name
order by max(o.cost) desc

would do. Please include your query and if relevant, table definitions for future questions.

Related Solutions

Postgresql – LISTEN / NOTIFY privileges

There are no permissions on LISTEN and NOTIFY. It would not have made sense to have any until support for notify payloads was introduced in more recent versions.

If you want access control, create a table with the information that you want and then send a NOTIFY that's empty or has nothing but a primary key for that table in it. SELECT the row(s) of interest from the table, which can have access control, to get the sensitive data. This used to be the only way to use LISTEN and NOTIFY to send specific data before payloads were added.

ORDER BY vs ROW_NUMBER for Top N Queries in PostgreSQL

First, to answer the questions implied in the comments, that the assignment of row numbers with the ROW_NUMBER() aggregate seems inefficient because we already have the COUNT(violation) numbers:

This is needed because the COUNT numbers can be different for each partition (class). Since we want the 2 top numbers (for each class), we can't find a useful condition for that. With the row numbers, we can use the WHERE v < 3 which gives us the top 2.

In version 9.3, the LATERAL joins were added in Postgres, which are similar to the CROSS and OUTER APPLY of SQL-Server. With this new kind of join, you can write a query that uses the COUNT numbers and a TOP 2 for each partition. Whether it is more or less efficient, you can test:

WITH classes AS
  ( SELECT DISTINCT class
    FROM people
  ) 
                             -- if you have a "classes" table, skip the above lines
SELECT 
    v.id, v.name, c.class,
    v.violations
FROM
    classes AS c,
  LATERAL 
    ( SELECT p.id, p.name,
             COUNT(d.violation) AS violations
      FROM people AS p
        INNER JOIN discipline AS d
          ON d.people_id = p.id
      WHERE p.class = c.class 
      GROUP BY p.id, p.name
      ORDER BY violations DESC
      LIMIT 2
  ) AS v
ORDER BY
    c.class, v.violations DESC ;

Tested at SQL-Fiddle

Best Answer

Related Solutions

Postgresql – LISTEN / NOTIFY privileges

ORDER BY vs ROW_NUMBER for Top N Queries in PostgreSQL

Related Question