[FIXED] Get only the latest status by MAX function for each Application using WHERE clausule instead of ORDER BY

Issue

I would like to obtain the same result of query using WHERE instead of GROUP BY. I have applications and each application has statuses with date. I need to return applications with their latest status date. My query with ORDER BY: (the result is correct)

select a.guid, MAX(s.request_time) as last_request_time 
from public.applications_status s inner join public.applications a on a.guid = s.guid_application
group by a.guid ;

result:

guid                                |last_request_time  |
------------------------------------|-------------------|
330d32d5-2496-4cce-9d11-29e59333766a|2020-07-22 13:06:25|
5b46cda9-b954-4d8b-82cf-f1d83f77b175|2020-07-22 13:07:25|
34071189-ab3d-47ff-9ee1-aca6fa806bc9|2020-08-03 10:45:15|
a8961058-a6ee-4d71-b325-9aca83b22237|2020-08-03 10:45:39|
ff98695f-e1a8-439e-8a6c-7991348b6cd7|2020-07-29 14:38:18|

I try this but it return me only the one application with latest status date:

select a.guid, s.request_time 
from public.applications_status s inner join public.applications a on a.guid = s.guid_application
where request_time = (select MAX(applications_status.request_time) from applications_status );

result:

guid                                |request_time       |
------------------------------------|-------------------|
a8961058-a6ee-4d71-b325-9aca83b22237|2020-08-03 10:45:39|

Applications table

CREATE TABLE public.applications (
    id bigserial NOT NULL,
    guid varchar(40) NOT NULL,
    "name" varchar(60) NOT NULL,
    latest_status_date timestamp NULL,
    latest_status bool NOT NULL,
    id_production bigserial NOT NULL,
    CONSTRAINT applications_guid_key UNIQUE (guid),
    CONSTRAINT applications_pkey PRIMARY KEY (id),
    CONSTRAINT uk_gtuqgycxk8ulkir3io2p49yn1 UNIQUE (guid),
    CONSTRAINT fkaid_prod FOREIGN KEY (id_production) REFERENCES productions(id) ON DELETE CASCADE
);

Applications_status table

CREATE TABLE public.applications_status (
    id bigserial NOT NULL,
    status bool NOT NULL,
    guid_application varchar(50) NOT NULL,
    log varchar(200) NULL,
    request_time timestamp NOT NULL,
    CONSTRAINT status_pkey PRIMARY KEY (id),
    CONSTRAINT fkaguid_application FOREIGN KEY (guid_application) REFERENCES applications(guid) ON UPDATE CASCADE ON DELETE CASCADE
);

Why I need this way? I try to return Applications with their latest status in Spring Boot using @Where annotation in @OneToMany relation in Entity.

@OneToMany(cascade = CascadeType.ALL, mappedBy = "application", fetch = LAZY)
@JsonManagedReference
@Where(clause = "request_time = (SELECT MAX(applications_status.request_time) FROM applications_status )")
@OrderBy("requestTime DESC")
private List<ApplicationStatus> applicationStatuses;

I also try to use @BatchSize(size = 1) but it doesn't work.

Solution

Through trial and error, I found a solution:

SQL query:

select a.guid, s.request_time 
from public.applications_status s inner join public.applications a on a.guid = s.guid_application
where request_time = (select MAX(applications_status.request_time) 
                        from applications_status 
                        where applications_status.guid_application = s.guid_application );

Spring-Boot:

@OneToMany(cascade = CascadeType.ALL, mappedBy = "application", fetch = LAZY)
@JsonManagedReference
@Where(clause = "request_time = (SELECT MAX(applications_status.request_time) FROM applications_status where guid_application = applications_status.guid_application )")
@OrderBy("requestTime DESC")
private List<ApplicationStatus> applicationStatuses;

Answered By - Seldo97