JdbcTemplate SELECT query to get unique rows (each row with unique ID is present once)

Anyone willing to help me out with one jdbctemplate query?

Need to get only rows with unique id’s but there are duplicates, because other columns, such as date have different values. I need to get the max date preferably and the resultset should not have any duplicates. 😡

select files.id, files.popularity, user_clicked_files.last_clicked from files inner join user_clicked_files on files.id = user_clicked_files.file_id where user_clicked_files.last_clicked > ? order by files.popularity desc limit 10

output:

[File [id=1a9227b2-d337-4c4b-a26c-42ed8c94de34, last_clicked='2022-05-30', popularity='8'], 
File [id=1a9227b2-d337-4c4b-a26c-42ed8c94de34, last_clicked='2022-06-03', popularity='8'], 
File [id=61f3860c-22b3-4c24-90bd-98c7f520fad7, last_clicked='2022-06-04', popularity='8'], 
File [id=61f3860c-22b3-4c24-90bd-98c7f520fad7, last_clicked='2022-06-03', popularity='8'], 
File [id=d70ff033-04cb-4205-acfe-2432f66525c2, last_clicked='2022-05-30', popularity='7'], 
File [id=d70ff033-04cb-4205-acfe-2432f66525c2, last_clicked='2022-05-30', popularity='7'], 
File [id=d70ff033-04cb-4205-acfe-2432f66525c2, last_clicked='2022-05-31', popularity='7'], 
File [id=9543b842-d592-46df-a63c-8e7c14791169, last_clicked='2022-06-04', popularity='7'], 
File [id=d70ff033-04cb-4205-acfe-2432f66525c2, last_clicked='2022-05-29', popularity='7'], 
File [id=d70ff033-04cb-4205-acfe-2432f66525c2, last_clicked='2022-06-04', popularity='7']]

This almost works, but not quite. There are duplicates sadly.

Here’s the two tables I’m working with.

CREATE TABLE files
(
    id         uuid DEFAULT gen_random_uuid() not null primary key,
    file_name  VARCHAR(255),
    popularity INTEGER
);
CREATE TABLE user_clicked_files
(
    user_id      uuid,
    file_id      uuid,
    last_clicked date,
    PRIMARY KEY (user_id, file_id)
);

PS.: Using PostgreSQL

Answer

Figured it out. Here’s the query.

select f.id, f.popularity, x.last_clicked
from files f
join (
  select file_id, max(last_clicked) as last_clicked
  from user_clicked_files
  where last_clicked > ?
  group by file_id) x
on (f.id=x.file_id)
order by f.popularity desc
limit 10