Retrieving the most recent communication from a user, and its accepted answer.
The original SQL query was an incomprehensible monster, including
- A main query ("A"), containing an inner query ("B") named
AggregatedData with an attribute name
maxmaxtime, which in turn used a
UNION of two queries ("C1" and "C2"), also known as
- Two uses of
SELECT DISTINCT to deduplicate subquery results
- The query parameter
@UserId mentioned four times
It languished as tumbleweed for over two years, probably mostly due to its complexity.
The solution clears up the query by introducing terminology that matches the way users normally think of messages: threads and interlocutors. It goes on to suggest a
VIEW that could simplify the query, as well as pointing out a dramatic simplification that could be possible using another RDBMS such as PostgreSQL.
By the way, even though the code in the question was a mess, the question was very well composed, including the background, schema, example data, and expected output.