Hi there.. I couldn't think of any better way to phrase the title, suppose we have these two tables:

CREATE TABLE "Users" (
"Id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
"Name" varchar Not NULL);

CREATE TABLE "Scores" (
"Id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
"UserId" integer,
"Date" bigint NOT NULL,
"Score" integer,
FOREIGN KEY("UserId") REFERENCES "Users"("Id") ON DELETE CASCADE);

​

And I want to query for a list of all users with their respective maximum score and the date they got it. Currently I query for the list of users, then for each user I query for all the scores in Scores table where UserId is whatever, ordered by Score descending, then by date, taking only one. It works, although it's a lot of I/O.

Other option would be to check if a score is a personal best whenever there's a new one and update rows "Best score" and "Date of Best" on users table, which would mean denormalizing the table so I'd rather avoiding that approach.

My question is, is there any way to get those rows together in a single query?Thanks in advance..

​

Ps: I edited the post a hundred times and I just can't get the line breaks right..