sql group by versus distinct

by

GROUP BY maps groups of rows to one row, per distinct value in specific columns, which don’t even necessarily have to be in the select-list.

SELECT b, c, d FROM table1 GROUP BY a;

This query is legal SQL (correction: only in MySQL; actually it’s not standard SQL and not supported by other brands). MySQL accepts it, and it trusts that you know what you’re doing, selecting b, c, and d in an unambiguous way because they’re functional dependencies of a.

However, Microsoft SQL Server and other brands don’t allow this query, because it can’t determine the functional dependencies easily. edit: Instead, standard SQL requires you to follow the Single-Value Rule, i.e. every column in the select-list must either be named in the GROUP BY clause or else be an argument to a set function.

Whereas DISTINCT always looks at all columns in the select-list, and only those columns. It’s a common misconception that DISTINCT allows you to specify the columns:

SELECT DISTINCT(a), b, c FROM table1;

Despite the parentheses making DISTINCT look like function call, it is not. It’s a query option and a distinct value in any of the three fields of the select-list will lead to a distinct row in the query result. One of the expressions in this select-list has parentheses around it, but this won’t affect the result.

More Related Contents:

  1. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  2. Converting SELECT DISTINCT ON queries from Postgresql to MySQL
  3. Calculate Average of the largest values in multiple columns
  4. MyISAM versus InnoDB [closed]
  5. MYSQL OR vs IN performance
  6. How to Perform an UPSERT so that I can use both new and old values in update part
  7. JDBC batch insert performance
  8. Storing JSON in database vs. having a new column for each key
  9. How to group by week in MySQL?
  10. MySQL LIMIT clause equivalent for SQL SERVER
  11. Why is MySQL InnoDB insert so slow?
  12. How do I decide when to use right joins/left joins or inner joins Or how to determine which table is on which side?
  13. Group mysql query by 15 min intervals
  14. How can I speed up a MySQL query with a large offset in the LIMIT clause?
  15. Is it possible to GROUP BY multiple columns using MySQL?
  16. Using Count to find the number of occurrences
  17. Import MySQL database into a SQL Server
  18. MySql views performance [closed]
  19. “SELECT COUNT(*)” is slow, even with where clause
  20. GROUP BY – do not group NULL
  21. MySQL load data infile – acceleration?
  22. MySQL SELECT most frequent by group
  23. MySQL – Meaning of “PRIMARY KEY”, “UNIQUE KEY” and “KEY” when used together while creating a table
  24. Why is connecting to MySQL server so slow?
  25. group by range in mysql
  26. Mysql performance on 6 million row table
  27. Composite Primary Key performance drawback in MySQL
  28. SQL query with avg and group by
  29. Convert SQL Server query to MySQL [duplicate]
  30. comparison of truncate vs delete in mysql/sqlserver [duplicate]

Leave a Comment