PostgreSQL: get the max values from a consult

Question

I need to get the max values from a list of values obtained from a query.

Basically, the problem is this:

I have 2 tables:

• Lawyer

  • id (PK)
  • surname
  • name

• Case

  • id (PK)
  • id_Client
  • date
  • id_Lawyer (FK)

And I need to get the Lawyer with the largest number of cases...(There is not problem with that) but, if exist more than one lawyer with the largest number of cases, I should list them. Any help on this would be appreciated.

Answer 1

SELECT l.*, cases
FROM  (
   SELECT "id_Lawyer", count(*) AS cases, rank() OVER (ORDER BY count(*) DESC) AS rnk
   FROM   "Case"
   GROUP  BY 1
   ) c
JOIN   "Lawyer" l ON l.id = c."id_Lawyer"
WHERE  c.rnk = 1;

Basics for the technique (like @FuzzyTree provided):

• PostgreSQL equivalent for TOP n WITH TIES: LIMIT "with ties"?

You only need a single subquery level since you can run window functions over aggregate functions:

• Get the distinct sum of a joined table column
• Best way to get result count before LIMIT was applied

Aside: It's better to use legal, lower case, unquoted identifiers in Postgres. Never use a reserved word like Case, that can lead to very confusing errors.