Groupwise MAX() on a subquery

Question

I'm attempting to calculate return the maximum value in a table, alongside the other values in that table. However, the table I'm doing this for is not a "real" table, it is one generated by a subquery. This gives me problems, as I don't think I can join to it twice, without respecifying the entire subquery.

I currently have a SQL Server solution to this, using ROW_NUMBER() OVER (PARTITION BY providerId ORDER BY partnershipSetScore DESC) rnk, but I'm looking for a DBMS agnostic version if possible, as the unit tests for the project run in a Sqlite DB which does not have this functionality.

Here is the schema and my SQL Server specific query, in case they are useful:

Course:

int id
varchar name
int schoolId

School:

int id
varchar name

Partnership:

int id
varchar partnershipName

SchoolPartnership:

int id
int schoolId
int partnershipId

Here's the query:

SELECT
    schoolId,
    partnershipId AS bestPartnershipSetId,
    partnershipScore AS bestPartnershipScore
FROM
(
    SELECT
        pp.schoolId,
        partnershipScores.partnershipId,
        partnershipScores.partnershipScore,
        ROW_NUMBER() OVER (PARTITION BY schoolId ORDER BY partnershipScore DESC) rnk
    FROM schoolPartnership pp
    INNER JOIN (
        SELECT
            pp.partnershipId,
            (
                (CASE WHEN SUM(CASE WHEN c.name LIKE '%French%' THEN 1 ELSE 0 END) > 0 THEN 1 ELSE 0 END)
                + (CASE WHEN SUM(CASE WHEN c.name LIKE '%History%' THEN 1 ELSE 0 END) > 0 THEN 1 ELSE 0 END)
            ) AS partnershipScore
        FROM schoolPartnership pp
        INNER JOIN course c ON c.schoolId = pp.schoolId
        GROUP BY partnershipId
    ) AS partnershipScores ON partnershipScores.partnershipId = pp.partnershipId
) AS schoolPartnershipScores
WHERE rnk = 1

If you need further information on what I'm trying to achieve, please see Custom sorting algorithm for a large amount of data : This query will be a subquery of a larger query that performs sorting of schools by the most suitable partnership.

Andriy M Andriy M · Accepted Answer · 2013-02-12T22:35:59

Perhaps, when talking about joining the subquery twice, you had this technique in your mind:

SELECT a.*
FROM atable a
INNER JOIN (
  SELECT
    col1,
    MAX(col2) AS max_col2
  FROM atable
  GROUP BY col1
) m
ON a.col1 = m.col1 AND a.col2 = m.max_col2
;

And that would have been perfectly fine to use as a DBMS-agnostic way (at least, one working both in SQL Server and SQLite) to do the job if this had been about a single table.

Instead, you've got a subquery. However, I can see no other method to accomplish what you are asking about. In this situation, therefore, I can see two options for you (one possibly inapplicable in your particular case, but still an option generally):

Do what you are trying to avoid, i.e. duplicate the subquery specifically for finding the aggregated values per group, then join it back to the same subquery, like above.
Persist the results of the subquery temporarily, then apply the above technique to the temporary result set.

The first option isn't very attractive indeed, all the less so since there's a hope that the second one might work.

One issue with the second option is that temporary datasets are implemented differently in SQL Server and in SQLite. In SQLite, you use a CREATE TEMPORARY TABLE statement for that. SQL Server doesn't support the TEMPORARY keyword in the context of a CREATE TABLE statement and instead uses a special character (#) at the beginning of the table name to denote that the table is in fact a temporary one.

So, the only workaround I can see is to use a normal table as a temporary storage. You could create it once and then delete its contents every time when running your query, just before inserting the temporary result set:

DELETE FROM TempTable;
INSERT INTO TempTable (
  schoolId,
  bestPartnershipSetId,
  bestPartnershipScore
)
SELECT
  pp.schoolId,
  partnershipScores.partnershipId,
  partnershipScores.partnershipScore,
FROM
  ...
;
SELECT ...
FROM TempTable
...
;

Or you could create & drop it every time you run the query:

CREATE TABLE TempTable (
  ...
);
INSERT INTO TempTable (...)
SELECT ...
FROM ...
;
SELECT ...
FROM TempTable
...
;
DROP TABLE TempTable;

Note that using a normal table as a temporary storage like this is not concurrency-friendly in SQL Server. If this may pose a problem, you'll probably have to abandon this option and end up with the first one. (But that's probably the costs you have to pay when you want a platform-independent solution, especially when the platforms are as different as SQL Server and SQLite are.)

Groupwise MAX() on a subquery

4 Answers