Tagged articles
group-by
55 articles · Page 1 of 1
This article tests Polars 2.0 against 1.43.2, revealing that join row order is no longer stable, Int64+UInt64 operations now return Int128 instead of Float64 for exact precision, and LazyFrame.collect() defaults to the streaming engine—though not all queries gain out-of-core capability.
Int128Polarsdataframe
0 likes · 9 min read

This article debunks the myth that HAVING requires GROUP BY, demonstrating advanced HAVING techniques including missing sequence detection, mode and median calculation, and NULL-aware grouping with concrete SQL examples and performance reasoning.
HAVING clauseNULL handlingSQL
0 likes · 13 min read

This article explains why MySQL GROUP BY + ORDER BY queries become extremely slow on hundred-million-row tables even when EXPLAIN shows index usage, covering temporary tables, filesort, loose index scan limitations, scanning overhead, and solutions like pre-aggregated summary tables, partitioning, columnar engines, and query scope reduction.
ClickHouseIndex OptimizationMySQL
0 likes · 12 min read

The article explains that Milvus 3.0 search aggregation operates on ANN candidates, not full collections, so missing buckets stem from candidate budget limits (topK and TopHits.size), not the safe_factor parameter; it distinguishes candidate-signal counts from exact filtered counts and advises choosing the right query path for each need.
ANNMilvusVector Database
0 likes · 14 min read

This article reviews MySQL's GROUP BY fundamentals, highlights its limitations and execution order, then demonstrates how window functions—including ROW_NUMBER, RANK, DENSE_RANK, cumulative totals, moving averages, and TOP N techniques—can overcome those gaps for richer analytical queries.
MySQLRankingSQL
0 likes · 11 min read

This article compares MySQL’s SELECT DISTINCT and GROUP BY clauses, explaining their syntax, functional differences, performance implications, and ideal scenarios through detailed examples, index usage analysis, and a feature comparison table, helping developers choose the right approach for deduplication or aggregation tasks.
MySQLSELECT DISTINCTSQL Performance
0 likes · 10 min read

This article explains the functional differences, performance considerations, and practical use cases of MySQL's SELECT DISTINCT and GROUP BY clauses, providing syntax details, example tables, query results, and a comparison table to help developers choose the right approach for deduplication or aggregation tasks.
MySQLSELECT DISTINCTSQL
0 likes · 8 min read

This article explains why a MySQL GROUP BY query can degrade from seconds to minutes as data grows, shows how to locate the bottlenecks with EXPLAIN and profiling tools, and provides practical indexing, query rewriting, temporary‑table tuning, batch aggregation, and distributed‑database strategies to restore performance.
MySQLSQLgroup-by
0 likes · 13 min read

This article explains ClickHouse’s core architecture—including its storage‑compute integration, MPP parallelism, columnar storage, vectorized execution, data pre‑sorting, table engines, sparse and auxiliary indexes, and the two‑stage aggregation pipeline—then walks through the exact GROUP BY execution flow for both local and distributed tables, illustrating each step with diagrams, SQL demos, and code snippets.
ClickHouseDistributed QueryHashMap
0 likes · 29 min read

This article explains why MySQL GROUP BY queries may run slowly, analyzes the execution plan showing temporary tables and filesort, and provides practical optimization techniques such as removing unnecessary sorting, adding indexes, adjusting buffer settings, and considering application‑level or materialized view solutions.
MySQLdatabasesgroup-by
0 likes · 4 min read

This article explains why MySQL GROUP BY queries may run slowly due to missing indexes, temporary tables, and filesort, and provides practical optimization techniques such as removing unnecessary sorting, adding indexes, tuning buffers, and using application‑level or materialized view solutions.
MySQLPerformance Tuninggroup-by
0 likes · 6 min read

This article demonstrates three SQL techniques—DISTINCT, GROUP BY, and the ROW_NUMBER window function—for deduplicating records and counting unique tasks, comparing their syntax, performance, and behavior across MySQL, Hive, and Oracle environments.
DISTINCTHiveOracle
0 likes · 5 min read

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.
HAVINGSQLaggregation
0 likes · 13 min read

This article compares MySQL's DISTINCT and GROUP BY clauses, detailing when they deliver identical performance with indexed columns, why DISTINCT can be faster without indexes, how each handles NULLs and multi‑column deduplication, and provides practical syntax examples and optimization guidance.
DISTINCTIndex OptimizationMySQL
0 likes · 10 min read

Both DISTINCT and GROUP BY can use indexes when available, giving similar performance, but without indexes DISTINCT is usually faster because GROUP BY may invoke implicit sorting; MySQL 8.0 removed this sorting, making their performance nearly equal, and GROUP BY offers clearer semantics and more flexibility for complex queries.
DISTINCTIndex OptimizationMySQL
0 likes · 9 min read

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.
Index ScanMySQLSQL Optimization
0 likes · 15 min read

When a system’s data grows, MySQL queries can become slow and cause latency; this article presents practical optimization strategies—including query tuning, pagination, join improvements, subquery replacement, order‑by and group‑by enhancements, and count() efficiency—illustrated with sample tables, code snippets, and performance screenshots.
MySQLORDER BYPagination
0 likes · 13 min read

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.
MySQLSQL HintUNION
0 likes · 13 min read

This article explains the true execution sequence of SQL statements—starting with FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and LIMIT—illustrating each step with diagrams, examples, and code snippets to clarify how databases process queries.
FROMHAVINGORDER BY
0 likes · 6 min read

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.
HAVINGMySQLSQL
0 likes · 4 min read

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.
DISTINCTMySQLONLY_FULL_GROUP_BY
0 likes · 6 min read

This article compares MySQL's DISTINCT and GROUP BY clauses, explaining how index availability, implicit sorting, and MySQL version differences affect their execution speed, and provides practical guidance on choosing the right clause for optimal query performance.
DISTINCTIndexMySQL
0 likes · 10 min read

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.
From Join WhereHAVINGLIMIT
0 likes · 8 min read

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.
FROMHAVINGLIMIT
0 likes · 5 min read

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.
DISTINCTIndexMySQL
0 likes · 8 min read

This article explains how DISTINCT and GROUP BY differ in MySQL, comparing their index usage, execution speed with and without indexes, the impact of implicit sorting in older versions, and why GROUP BY is often preferred for clearer semantics and advanced aggregations.
DISTINCTMySQLSQL Performance
0 likes · 10 min read

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.
DISTINCTMySQLSQL
0 likes · 9 min read

This article breaks down the step-by-step SQL execution sequence—from FROM and JOIN through WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, to LIMIT—illustrating each phase with examples, diagrams, and code snippets to help you understand how queries are processed and optimized.
FROMHAVINGLIMIT
0 likes · 7 min read

This guide explains how to identify duplicate rows in a MySQL table using GROUP BY and HAVING, how to delete the extra rows while keeping the earliest entry, and how to handle duplicate detection across multiple columns with correct query patterns and common pitfalls.
DELETEHAVINGMySQL
0 likes · 11 min read

This guide explains three SQL techniques—DISTINCT, GROUP BY, and the ROW_NUMBER window function—for deduplicating records, compares their behavior and performance, and provides concrete query examples with a sample task table and additional test cases.
DISTINCTROW_NUMBERSQL
0 likes · 5 min read

This article explains how MySQL can implement GROUP BY using either a compact index scan that reads rows sequentially or a loose index scan that jumps to the needed rows, compares their costs, and shows when each method is optimal, including a hybrid sequential‑loose scan.
MySQLcompact index scangroup-by
0 likes · 15 min read

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.
DISTINCTROW_NUMBERSQL
0 likes · 4 min read

The article explains the true execution order of SQL queries, detailing each step from FROM and JOIN through WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and LIMIT, and illustrates the concepts with examples and code snippets.
FROMHAVINGLIMIT
0 likes · 6 min read

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.
Database QueriesMySQLSQL Tutorial
0 likes · 12 min read

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.
DISTINCTROW_NUMBERSQL
0 likes · 4 min read

This guide explains step‑by‑step how to identify and delete duplicate records in MySQL tables, covering simple SELECT checks, handling MySQL’s update‑from limitation, and fast deletion techniques that keep one record per duplicate group.
DELETEMySQLSQL
0 likes · 5 min read

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.
MySQLSQLdatabase
0 likes · 5 min read

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.
IndexMySQLgroup-by
0 likes · 13 min read

This article explains the purpose and proper use of the SQL HAVING clause, demonstrates how it works with GROUP BY to filter aggregated groups, shows examples for finding groups with specific counts, missing IDs, mode, median, and non‑NULL values, and clarifies best practices for query performance and semantics.
HAVINGSQLaggregation
0 likes · 13 min read

This article explains how MySQL versions prior to 8.0 automatically sorted GROUP BY results, why the optimizer added this hidden ordering, how the behavior was removed in MySQL 8.0, and what developers must watch for when migrating, illustrated with concrete SQL examples and EXPLAIN output.
MySQLSQLgroup-by
0 likes · 11 min read

This article explains how to identify duplicate rows in a MySQL table using GROUP BY and HAVING, shows several SELECT queries to list duplicates, and provides multiple DELETE strategies—including sub‑queries and multi‑column handling—to safely remove excess records while keeping one copy.
DELETEMySQLSQL
0 likes · 9 min read

This article explains the relational model, its data structures, set-oriented operations, and integrity constraints, then demonstrates core SQL clauses such as SELECT, WHERE, GROUP BY, UNION, INTERSECT, EXCEPT, and various JOIN types with clear examples and diagrams.
SQLSet Theorydatabase
0 likes · 12 min read

This article explains the SQL standard restriction on SELECT columns after GROUP BY, demonstrates the error caused by ONLY_FULL_GROUP_BY, explores MySQL SQL modes that affect this behavior, and clarifies why aggregated queries cannot reference non‑grouped columns.
SQLSQL Modesaggregation
0 likes · 11 min read

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.
MySQLSQLaggregation
0 likes · 11 min read

This article explains the SQL standard limits on SELECT columns after GROUP BY, demonstrates how MySQL's ONLY_FULL_GROUP_BY mode enforces those limits, explores various SQL modes, and clarifies why aggregated queries cannot reference non‑grouped columns directly.
MySQLONLY_FULL_GROUP_BYSQL
0 likes · 11 min read

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 IndexesMySQLTemporary tables
0 likes · 12 min read

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.
CASE WHENDatabase TutorialSQL
0 likes · 10 min read

This guide explains how to define duplicate rows, use GROUP BY and HAVING to locate them, and apply temporary tables and DELETE statements to remove duplicates while preserving the row with the smallest ID.
DELETEHAVINGSQL
0 likes · 11 min read

This article shows step‑by‑step how to identify duplicate rows in a MySQL table—whether the duplication is based on a single column, multiple columns, or either column—and provides reliable SQL patterns, including GROUP BY with HAVING, UNION, sub‑queries, temporary tables, and a safe DELETE statement to clean the data.
DELETEHAVINGMySQL
0 likes · 11 min read

This article shows how to identify and remove duplicate rows in a MySQL table by defining duplication, using GROUP BY with HAVING, creating temporary tables with MIN, and applying various techniques—including UNION, nested subqueries, and joins—to handle single‑column and multi‑column duplicate detection.
HAVINGMySQLSQL
0 likes · 11 min read


ITPUBJun 14, 2016 · Databases
This guide explains how to locate rows with identical values across one or several columns using self‑joins, GROUP BY, DISTINCT, and rowid, and provides step‑by‑step SQL statements to safely delete the extra duplicate entries from a table.
DELETEDISTINCTSQL
0 likes · 7 min read


ITPUBApr 3, 2016 · Databases
This guide explains how to use SQL's GROUP BY clause with examples covering basic grouping, GROUP_CONCAT, aggregate functions, HAVING filters, multi‑column grouping, WITH ROLLUP, and inner, left, and right joins, illustrating each technique with clear query results.
HAVINGSQLWITH ROLLUP
0 likes · 7 min read

Distinct in MySQL works like GROUP BY but returns only one row per group; it can use index scans like GROUP BY, falls back to a temporary table when indexes aren't sufficient, and unlike GROUP BY it avoids sorting, making index optimization crucial for large result sets.
DISTINCTIndex OptimizationMySQL
0 likes · 2 min read

When MySQL's query optimizer cannot use a suitable index for a GROUP BY, it reads the needed rows, creates a temporary table, and performs sorting, which can degrade performance unless sort_buffer_size is properly configured and large result sets are avoided.
MySQLgroup-bytemporary table
0 likes · 3 min read

This article explains why GROUP BY requires sorting like ORDER BY, how a suitable index can satisfy both operations, and outlines the precise conditions—including single‑table queries, left‑most index prefixes, constant values, and limited aggregates—that allow MySQL to execute GROUP BY using an index scan.
Index OptimizationMySQLQuery Performance
0 likes · 3 min read
