How to write query/create view to limit multiple records to show only max value

Question

Consider the following three tables. A list of contacts, a list of status with a defined "rank" and a join table that links a contact to multiple status's.

   CREATE TABLE public."Contacts"
   (
     name character varying COLLATE pg_catalog."default",
     email character varying COLLATE pg_catalog."default",
     contactid integer NOT NULL DEFAULT nextval('"Contacts_contactid_seq"'::regclass),
     CONSTRAINT "Contacts_pkey" PRIMARY KEY (contactid)
   )
   CREATE TABLE public.statusoptions
   (
     option character varying COLLATE pg_catalog."default" NOT NULL,
     "Rank" integer,
     CONSTRAINT "ListOptions_pkey" PRIMARY KEY (option)
   )

   CREATE TABLE public."ContactStatus"
   (
     contactid integer NOT NULL,
     option character varying COLLATE pg_catalog."default" NOT NULL,
     CONSTRAINT "Options_pkey" PRIMARY KEY (contactid, option),
     CONSTRAINT fk_1 FOREIGN KEY (contactid)
      REFERENCES public."Contacts" (contactid) MATCH SIMPLE
      ON UPDATE NO ACTION
    ON DELETE NO ACTION,
    CONSTRAINT fk_2 FOREIGN KEY (option)
    REFERENCES public.statusoptions (option) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    )

The following query returns all rows.

  select "Contacts".contactid, "Contacts".name, "ContactStatus".option, statusoptions."Rank" as 
  currentRank
  from "Contacts","ContactStatus", statusoptions
  where "Contacts".contactid = "ContactStatus".contactid
   and statusoptions.option="ContactStatus".option

This returns a record set that looks like this:

          Contactid name        Status          CurrentRank
           1        "john"      "apply"             1
           1        "john"      "Manager Review"    4
           2        "bill"      "apply"             1
           2        "bill"      "1st interview"     2
           1        "john"      "1st interview"     2

What I need is to create a query/view that would always JUST return the rows of the MAX current RANK. So the expected result I want from this view is:

         Contactid      name        Status          CurrentRank
          1           "john"        "Manager Review"    4
          2           "bill"        "1st interview"     2

At any time, I could change the "Rank" value in the statusoptions field, which would change the view accordingly.

Is this possible?

score 0 · Answer 1 · answered Nov 25 '19 at 16:00

You can use distinct on:

select distinct on(c.contactid)
    c.contactid, 
    c.name, 
    cs.option, 
    s."Rank" as currentRank
from 
    "Contacts" c
    inner join "ContactStatus" cs on c.contactid = cs.contactid
    inner join statusoptions s on s.option = cs.option
order by c.contactid, s."Rank" desc

Note:

always use explicit, standard joins (with the on clause) instead of old-school, implicit joins (with a comma in the where clause)
(short) table aliase make the query shorter and easier to read
consider avoiding quoting table and column names, unless when absolutly necessary; they make the identifiers case-senstive, while by default they are not

Gordon Linoff · Answer 2 · 2019-11-25T16:11:13.840

0

In Postgres, you can use distinct on

I think you want:

select distinct on (c.contactid) c.contactid, c.name, cs.option, so."Rank" as currentRank
from "Contacts" c join
     "ContactStatus" cs
     on c.contactid = cs.contactid join
     statusoptions so
     on so.option = cs.option
order by c.contactid, so.rank desc;

Notes:

Use proper, explicit, standard JOIN syntax.
Never use commas in the FROM clause.
Table aliases make a query easier to write and to read.
You should avoid quoting table names and column names. That just clutters up queries unnecessarily.
distinct on usually has better performance than alternatives such as row_number().

edited Nov 25 '19 at 16:11

answered Nov 25 '19 at 16:01

Gordon Linoff

1,242,037
58
646
786

Thx! Exactly what I was looking for. I don't like to use quotes, but PGadmin complains about these tables If I don't put quotes around them. – mike hennessy Nov 25 '19 at 16:07
@mikehennessy . . . They were defined with quotes, so the names are case-sensitive. – Gordon Linoff Nov 25 '19 at 22:24

Bar · Answer 3 · 2019-11-25T16:22:52.767

0

You can do max(rank) and group by the remaining fields

select c.contactid, c.name, cs.option, max(so.rank) currentRank 

from Contacts c
join ContactStatus cs on c.contactid = cs.contactid
join StatusOptions so on so.option = cs.option

group by c.contactid, c.name, cs.option

edited Nov 25 '19 at 16:22

answered Nov 25 '19 at 16:05

Bar

56
4

How to write query/create view to limit multiple records to show only max value

3 Answers3