Q:

How to get key of the maximal record in group?

I have two tables in my database, one holds the names of files, and other holds records of information described in them, inincluding sizes of sections. it can be descrived as:
Table1: id as integer, name as varchar
Table2: recid as integer primary key, file_id as integer, score as float
Between the tables there is an one-to-many link, from Table1.id to table2.file_id. What i need is for every file which name matches a certain pattern retrieve the id of the linked record with the maximum score and the score itself.
So far i have used:
SELECT name,MAX(score)
FROM Table1
LEFT OUTER JOIN Table2 ON Table2.file_id=Table1.id
WHERE name LIKE :pattern
GROUP BY name

but i cannot retrieve the id of the record in Table2 this way.
The dialect i am using is Sqlite.
What query should be used to retrieve data on the record that has maximum score for every file?
Update:
With this query, i am getting close to what i want:
SELECT name,score,recid
FROM Table1
LEFT OUTER JOIN Table2 ON file_id=id 
WHERE name LIKE :pattern
GROUP BY name 
HAVING score=MAX(score)

However, this leaves out the entries in the first table that have no corresponding entries in the second table out. How can i ensure they are in the end result anyway? Should i use UNION, and if so - how?

A:

This can actually be achieved without a GROUP BY by using a brilliantly simple technique described by @billkarwin here:
SELECT name, t2.score
FROM Table1 t1
LEFT OUTER JOIN Table2 t2 ON t2.file_id = t1.id
LEFT OUTER JOIN Table2 t2copy ON t2copy.file_id = t2.file_id
                             AND t2.score < t2copy.score
WHERE name LIKE :pattern
  AND t2copy.score IS NULL

See SQL Fiddle demo.

