BestHub
Sign in
Tag

GROUP BY

0 views collected around this technical thread.

Rare Earth Juejin Tech Community
Rare Earth Juejin Tech Community
Oct 19, 2024 · Databases

Understanding the HAVING Clause in SQL: Concepts, Examples, and Best Practices

This article explains the purpose and proper use of the SQL HAVING clause, contrasts it with WHERE, and provides multiple practical examples—including counting groups, detecting missing IDs, calculating mode and median, and filtering fully‑submitted records—while highlighting common pitfalls and performance considerations.

AggregationDatabaseGROUP BY
0 likes · 13 min read
Understanding the HAVING Clause in SQL: Concepts, Examples, and Best Practices
Aikesheng Open Source Community
Aikesheng Open Source Community
Jul 8, 2024 · Databases

Optimizing MySQL GROUP BY with Index Scans: Loose vs Tight Index Scan

This article explains how MySQL GROUP BY queries can be dramatically accelerated by using appropriate index scans, compares Loose Index Scan and Tight Index Scan, shows practical test data, execution plans, and provides guidelines for when each method is most effective.

GROUP BYLoose Index ScanMySQL
0 likes · 15 min read
Optimizing MySQL GROUP BY with Index Scans: Loose vs Tight Index Scan
Aikesheng Open Source Community
Aikesheng Open Source Community
Nov 7, 2023 · Databases

When Does MySQL Use Internal Temporary Tables? Analysis of UNION, GROUP BY and Other Scenarios

This article explains the circumstances under which MySQL creates internal temporary tables, illustrating the behavior with UNION, UNION ALL, GROUP BY, various hints, and additional query patterns, and provides practical examples and execution plans to help developers understand and optimise their queries.

GROUP BYMySQLSQL Hint
0 likes · 13 min read
When Does MySQL Use Internal Temporary Tables? Analysis of UNION, GROUP BY and Other Scenarios
Full-Stack Internet Architecture
Full-Stack Internet Architecture
Aug 22, 2023 · Databases

Querying Students Enrolled in Both Course 1 and Course 2 with MySQL

This article explains how to retrieve the names of students who have selected both course 1 and course 2 in a MySQL table by using a GROUP BY clause together with a HAVING condition that counts distinct course IDs, avoiding incorrect results from a simple IN filter.

Database QueryGROUP BYHAVING
0 likes · 4 min read
Querying Students Enrolled in Both Course 1 and Course 2 with MySQL
Aikesheng Open Source Community
Aikesheng Open Source Community
May 10, 2023 · Databases

Retrieving Unique Code Values with Corresponding Dates and Totals in MySQL

This article explains how to obtain distinct code entries together with their cdate and ctotal fields in MySQL, covering the use of DISTINCT, GROUP BY, handling ONLY_FULL_GROUP_BY mode, and alternative solutions such as GROUP_CONCAT with DISTINCT.

DISTINCTDatabaseGROUP BY
0 likes · 6 min read
Retrieving Unique Code Values with Corresponding Dates and Totals in MySQL
Architecture Digest
Architecture Digest
Feb 22, 2023 · Databases

Understanding the Execution Order of SQL Queries

This article explains the logical execution sequence of a typical SQL query, detailing each phase from FROM/JOIN/WHERE through GROUP BY, HAVING, SELECT, ORDER BY, and LIMIT, and illustrating the process with diagrams and code examples.

DatabaseFrom Join WhereGROUP BY
0 likes · 8 min read
Understanding the Execution Order of SQL Queries
Java Architect Essentials
Java Architect Essentials
Feb 19, 2023 · Databases

Understanding the Execution Order of SQL Queries

This article explains the step‑by‑step execution order of a standard SQL query, covering FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY and LIMIT, and provides illustrative code snippets and diagrams to clarify each stage.

FROMGROUP BYHAVING
0 likes · 5 min read
Understanding the Execution Order of SQL Queries
Laravel Tech Community
Laravel Tech Community
Feb 16, 2023 · Databases

DISTINCT vs GROUP BY in MySQL: Performance and Usage Guide

This article explains the differences between DISTINCT and GROUP BY in MySQL, showing that with an index they have similar efficiency, while without an index DISTINCT is usually faster because GROUP BY may trigger sorting and temporary tables, and it provides syntax, examples, and recommendations.

DISTINCTGROUP BYMySQL
0 likes · 8 min read
DISTINCT vs GROUP BY in MySQL: Performance and Usage Guide
Selected Java Interview Questions
Selected Java Interview Questions
Dec 15, 2022 · Databases

DISTINCT vs GROUP BY in MySQL: Performance, Index Usage, and Implicit Sorting

This article explains the functional differences between DISTINCT and GROUP BY in MySQL, analyzes their performance with and without indexes, describes how implicit sorting affects GROUP BY before MySQL 8.0, and provides practical recommendations for choosing the appropriate clause.

DISTINCTDatabaseGROUP BY
0 likes · 9 min read
DISTINCT vs GROUP BY in MySQL: Performance, Index Usage, and Implicit Sorting
Laravel Tech Community
Laravel Tech Community
Apr 11, 2022 · Databases

Three Methods for SQL Deduplication: DISTINCT, GROUP BY, and ROW_NUMBER

This article explains three common SQL deduplication techniques—using DISTINCT, GROUP BY, and the ROW_NUMBER window function—illustrating their syntax, performance considerations, and example queries for counting unique task identifiers in relational databases.

DISTINCTGROUP BYROW_NUMBER
0 likes · 4 min read
Three Methods for SQL Deduplication: DISTINCT, GROUP BY, and ROW_NUMBER
Architecture & Thinking
Architecture & Thinking
Dec 27, 2021 · Databases

Master MySQL GROUP BY: From Basics to Advanced Aggregations

This guide explains MySQL GROUP BY syntax, the role of aggregate functions such as AVG, COUNT, MAX, MIN, and SUM, and demonstrates practical queries with single‑field and multi‑field grouping, filtering with WHERE and HAVING, ordering, limiting results, and the correct execution order of keywords.

GROUP BYMySQLSQL aggregation
0 likes · 12 min read
Master MySQL GROUP BY: From Basics to Advanced Aggregations
Laravel Tech Community
Laravel Tech Community
Nov 11, 2021 · Databases

Using DISTINCT, GROUP BY, and ROW_NUMBER for De‑duplication in SQL

This article explains how to remove duplicate rows in SQL by using DISTINCT, GROUP BY, and the ROW_NUMBER window function, compares their syntax and performance, and provides concrete examples with a Task table and a Test helper table.

DISTINCTGROUP BYROW_NUMBER
0 likes · 4 min read
Using DISTINCT, GROUP BY, and ROW_NUMBER for De‑duplication in SQL
Aikesheng Open Source Community
Aikesheng Open Source Community
Jul 5, 2021 · Databases

Fetching the Latest Record per Name Group in MySQL 5.6/5.7

The article explains why a simple GROUP BY query in MySQL 5.6 returns the earliest rows instead of the most recent ones, demonstrates how to correctly obtain the latest record per name using sub‑queries or window functions, and discusses the impact of the ONLY_FULL_GROUP_BY mode in MySQL 5.7.

DatabaseGROUP BYMySQL
0 likes · 5 min read
Fetching the Latest Record per Name Group in MySQL 5.6/5.7
Selected Java Interview Questions
Selected Java Interview Questions
May 5, 2021 · Databases

Understanding MySQL Temporary Tables, MEM_ROOT Memory Management, and Query Optimization

This article explains how MySQL processes a GROUP BY query on a user view table, detailing the creation and use of memory and disk temporary tables, the MEM_ROOT allocation mechanism, the execution stages revealed by EXPLAIN, and how adding a composite index can eliminate temporary tables and filesort for better performance.

GROUP BYMemory ManagementMySQL
0 likes · 13 min read
Understanding MySQL Temporary Tables, MEM_ROOT Memory Management, and Query Optimization
Top Architect
Top Architect
Sep 14, 2020 · Databases

Why SELECT Columns Not in GROUP BY Cannot Be Referenced After Aggregation in MySQL

This article explains the SQL standard restriction that only grouped keys, aggregate functions, or constants may appear in the SELECT clause of a GROUP BY query, demonstrates the resulting error in MySQL, and discusses how SQL modes like ONLY_FULL_GROUP_BY enforce this rule.

AggregationDatabaseGROUP BY
0 likes · 11 min read
Why SELECT Columns Not in GROUP BY Cannot Be Referenced After Aggregation in MySQL
Aikesheng Open Source Community
Aikesheng Open Source Community
Aug 13, 2019 · Databases

Understanding MySQL GROUP BY Execution Strategies

This article explains why MySQL GROUP BY can dominate query cost, describes the four execution methods—index‑ordered, external‑sort (filesort), temporary‑table, and index‑skip‑scan for MIN/MAX—shows how filtering interacts with grouping, and offers practical tips such as using FORCE INDEX to obtain the optimal plan.

Database IndexesGROUP BYMySQL
0 likes · 12 min read
Understanding MySQL GROUP BY Execution Strategies
Java Captain
Java Captain
Aug 7, 2019 · Databases

Practical Guide to Group, Join, and CASE WHEN Queries in SQL

This article provides a beginner‑friendly, step‑by‑step tutorial on common SQL operations—including GROUP BY aggregation, JOIN types, CASE WHEN expressions, and useful built‑in functions—illustrated with clear examples, typical pitfalls, and ready‑to‑use code snippets.

FunctionsGROUP BYSQL
0 likes · 10 min read
Practical Guide to Group, Join, and CASE WHEN Queries in SQL