Skip to content

Instantly share code, notes, and snippets.

@hackimov

hackimov/GROUP_MAXIMUM.SQL

Last active May 31, 2021 06:26
Show Gist options
  • Save hackimov/9649280688e3789b2537a1f7fc6750f6 to your computer and use it in GitHub Desktop.
Save hackimov/9649280688e3789b2537a1f7fc6750f6 to your computer and use it in GitHub Desktop.
Download ZIP
#SQL #MAXIMUM #WITHOUT_GROUP_BY #SPEED #QUERY
Raw
GROUP_MAXIMUM.SQL
SELECT maintable.*
FROM TABLE_NAME maintable
LEFT OUTER JOIN TABLE_NAME temporarytable
ON maintable.GROUPING_BY_COLUMN = temporarytable.GROUPING_BY_COLUMN
AND maintable.COLUMN_WHERE_THE_MAXIMUM_IS_NEEDED < temporarytable.COLUMN_WHERE_THE_MAXIMUM_IS_NEEDED
WHERE temporarytable.COLUMN_WHERE_THE_MAXIMUM_IS_NEEDED IS NULL
ORDER BY PRIMARY_KEY_COLUMN DESC
LIMIT 50;
@hackimov
Copy link
Author

An alternative way to get the maximum value from a group. This query does not require aggregation, as is the case with “GROUP BY”.

In addition, when grouping using “GROUP BY”, each of the groups is sorted by primary key, which also takes a lot of time.

My query compares the values of one table with another. Until he can find nothing more. If nothing else is found, then this is the maximum.

This query can help you save time getting the maximum value from the group.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment