PostgreSQL GROUP BY different from MySQL?

by

MySQL’s totally non standards compliant GROUP BY can be emulated by Postgres’ DISTINCT ON. Consider this:

MySQL:

SELECT a,b,c,d,e FROM table GROUP BY a

This delivers 1 row per value of a (which one, you don’t really know). Well actually you can guess, because MySQL doesn’t know about hash aggregates, so it will probably use a sort… but it will only sort on a, so the order of the rows could be random. Unless it uses a multicolumn index instead of sorting. Well, anyway, it’s not specified by the query.

Postgres:

SELECT DISTINCT ON (a) a,b,c,d,e FROM table ORDER BY a,b,c

This delivers 1 row per value of a, this row will be the first one in the sort according to the ORDER BY specified by the query. Simple.

Note that here, it’s not an aggregate I’m computing. So GROUP BY actually makes no sense. DISTINCT ON makes a lot more sense.

Rails is married to MySQL, so I’m not surprised that it generates SQL that doesn’t work in Postgres.

More Related Contents:

  1. MySQL not using indexes with WHERE IN clause?
  2. Best way to count rows by arbitrary time intervals
  3. Perform this hours of operation query in PostgreSQL
  4. Query that ignore the spaces
  5. PostgreSQL where all in array
  6. SQL where joined set must contain all values but may contain more
  7. How do you write a case insensitive query for both MySQL and Postgres?
  8. Find records where join doesn’t exist
  9. Rails 4 LIKE query – ActiveRecord adds quotes
  10. Prepared Statement on Postgresql in Rails
  11. How do I update if exists, insert if not (AKA “upsert” or “merge”) in MySQL?
  12. Natural Sort in MySQL
  13. PostgreSQL “Column does not exist” but it actually does
  14. Is SELECT or INSERT in a function prone to race conditions?
  15. Any downsides of using data type “text” for storing strings?
  16. MySQL get row position in ORDER BY
  17. MySQL unknown column in ON clause
  18. Maintaining order in MySQL “IN” query
  19. Postgres analogue to CROSS APPLY in SQL Server
  20. Select TOP X (or bottom) percent for numeric values in MySQL
  21. Postgres not using index when index scan is much better option
  22. Hamming distance on binary strings in SQL
  23. Which datatype should be used for currency?
  24. How to return two columns with function
  25. SELECT DISTINCT is slower than expected on my table in PostgreSQL
  26. What does collation mean?
  27. How to use SQL LIKE condition with multiple values in PostgreSQL?
  28. Row Rank in a MySQL View
  29. Select Rows with Maximum Column Value group by Another Column
  30. Is there a postgres CLOSEST operator?

Leave a Comment