0

I have the following table.

id user_id file_id completed updated
1 161 10 1 2022-10-11
2 164 11 1 2022-10-12
3 161 10 1 2022-10-12
4 167 10 1 2022-10-10
5 167 10 1 2022-10-11
6 167 10 1 2022-10-12

I want to select the row for each user having the max updated date for each file_id.

SELECT * FROM user_file
WHERE updated = (SELECT uf.updated FROM user_file uf GROUP BY uf.user_id,uf.file_id)

I have come up with this query but it returns an error "Subquery returns more than 1 row"

Dula
  • 1,276
  • 5
  • 14
  • 23
  • Possible duplicate of https://stackoverflow.com/questions/6807854/sql-query-to-get-column-values-that-correspond-with-max-value-of-another-column?rq=1 – Roemer Aug 02 '22 at 02:47
  • First, you're missing the MAX around the subquery, Secondly, you are basically selecting all users with a certain updated date. There may be many, that can never be the only selection criteria: select all users with this updated date. You want to have a specific link with the group by of the subquery. Look up JOIN's. – Roemer Aug 02 '22 at 02:51
  • But I can not use ID to join tables because the ID is just an auto incrementing integer. `user_id` and `file_id` are the ones that does matter – Dula Aug 02 '22 at 02:59
  • So join on those fields. It is quite simple but based on the limited info I have about your situation I would say: do not go into query designing before educating yourself thoroughly on the basics of relational databases. It is essential you get the basics first, especially the basics on joining tables. – Roemer Aug 03 '22 at 10:33

1 Answers1

0

Maybe you want to check these out?

Personally prefer the window function solution

SELECT a.*
  FROM (SELECT id, user_id, file_id, ...
               ROW_NUMBER() OVER (PARTITION BY file_id ORDER BY updated DESC) ranked_order
          FROM user_file) a
 WHERE a.ranked_order = 1
sexy_pikachu
  • 26
  • 3