How to get key of the maximal record in group?

Question

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?

score 3 · Accepted Answer · edited May 23 '17 at 10:27

3

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.

edited May 23 '17 at 10:27

Community

1
1

answered Aug 12 '13 at 10:25

Steve Chambers

37,270
24
156
208

Agreed - shame I can't really take credit for it. One of those where it's obvious *once you've seen it*! – Steve Chambers Aug 12 '13 at 11:52

score 1 · Answer 2 · answered Aug 12 '13 at 10:26

1

I think that you must use a subquery

SELECT name, recid, score
FROM Table1
LEFT OUTER JOIN Table2 ON Table2.file_id=Table1.id
WHERE name LIKE :pattern AND score = (SELECT MAX(score) FROM Table2.score)

answered Aug 12 '13 at 10:26

Francisco Puga

23,869
5
48
64

This will return entries with score equal to the maximum score in the whole of table 2, not in the groupings – Srv19 Aug 12 '13 at 10:52

score 0 · Answer 3 · answered Aug 12 '13 at 11:09

I think the easiest way to do this is with a correlated subquery:

SELECT name, recid, score
FROM Table1 LEFT OUTER JOIN
     Table2
     ON Table2.file_id=Table1.id
WHERE name LIKE :pattern AND
      score = (SELECT MAX(t2.score)
               FROM Table1 t1 LEFT OUTER JOIN
                    Table2 t2 
                    ON t2.file_id=t1.id
               where t1.name = table1.name
              );

Note that you need table aliases to distinguish the tables in the inner query from the outer query. I am guessing which tables the columns are actually coming from.

How to get key of the maximal record in group?

3 Answers3

Linked