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

Question

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.

`request_time = (select MAX(applications_status.request_time) from applications_status` not good because `time` is not unique kind of data — T.S., Aug 03 '20 at 14:46

Gordon Linoff · Answer 1 · 2020-08-03T14:59:41.410

0

The question is tagged both "sql" and "postgres", so this is a Postgres solution.

Use distinct on:

select distinct on (a.guid) a.*, s.*
from public.applications_status s inner join
     public.applications a
     on a.guid = s.guid_application
order by a.guid, s.request_time desc;

distinct on is a very handy Postgres extension that returns one row (the "first" row) for each group in the parentheses. The particular row is based on the order by.

edited Aug 03 '20 at 14:59

answered Aug 03 '20 at 14:46

Gordon Linoff

1,242,037
58
646
786

Answer is correct for query directly in database but I need to use it in `@Where` in Spring Boot. – Seldo97 Aug 03 '20 at 14:53
@Seldo97 . . . I clarified that the answer is only for the database. – Gordon Linoff Aug 03 '20 at 14:59

score 0 · Accepted Answer · answered Aug 03 '20 at 15:43

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;

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

2 Answers2

Linked