CODEWITHSUNDEEP
sqlpostgresql
Cheryl
Cheryl

Reputation: 245

How to take the max value for every element in SQL

CREATE TABLE Department(
    DNumber integer NOT NULL PRIMARY KEY);

CREATE TABLE Patient(
    PID integer NOT NULL PRIMARY KEY,
    P_DNumber integer NOT NULL REFERENCES Department (DNumber)
);

CREATE TABLE Appointment(
    AppNumber integer NOT NULL PRIMARY KEY,
    AppDate date NOT NULL,
    App_DNumber integer  NOT NULL  REFERENCES Department (DNumber)
);

CREATE TABLE Attend(
    A_PID integer NOT NULL REFERENCES Patient (PID),
    A_AppNumber integer NOT NULL REFERENCES Appointment(AppNumber)
);

HEllo, I have these tables and I want to find for each PID the last Date he attended an appointment. I tried with joins but nothing worked. I am using Postgres. Does anyone have an idea about it?

Thank you very much

Upvotes: 2

Views: 144

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270609

Give this a try, joining through Attend and Appointment. 

SELECT 
  Patient.PID,
  MAX(Appointment.AppDate) AS lastAttended
FROM 
  Patient 
  LEFT JOIN Attend ON Patient.PID = Attend.A_PID
  JOIN Appointment ON Appointment.AppNumber = Attend.A_AppNumber
GROUP BY Patient.PID

For patients attending 2+ appointments per month you need COUNT() inside a HAVING clause.

SELECT 
  Patient.PID,
  COUNT(Attend.A_AppNumber) AS numAppts
FROM 
  Patient 
  LEFT JOIN Attend ON Patient.PID = Attend.A_PID
GROUP BY Patient.PID
HAVING COUNT(Attend.A_AppNumber) >= 2

Upvotes: 4

Related Questions