{"id":1133,"date":"2013-12-17T20:07:38","date_gmt":"2013-12-17T08:07:38","guid":{"rendered":"http:\/\/p-s.co.nz\/wordpress\/?p=1133"},"modified":"2013-12-17T20:07:38","modified_gmt":"2013-12-17T08:07:38","slug":"dont-need-no-stinkin-group_concat","status":"publish","type":"post","link":"http:\/\/p-s.co.nz\/wordpress\/dont-need-no-stinkin-group_concat\/","title":{"rendered":"Don&#8217;t need no stinkin&#8217; GROUP_CONCAT"},"content":{"rendered":"<p>One of the things I loved about MySQL was GROUP CONCAT. A very, very useful function. I thought it was something I was going to have to learn to live without having shifted to PostgreSQL. But PostgreSQL has STRING_AGG() (string aggregate) which does much the same thing &#8211; who knew?! <a href=\"http:\/\/illuminatedcomputing.com\/posts\/2012\/06\/postgres_aggregate_functions\/\" target=\"_blank\">Others have also been surprised.<\/a>.<\/p>\n<p>Here is an example:<\/p>\n<blockquote><p>SELECT country, <strong>string_agg<\/strong>(city, &#8216;, &#8216; ORDER BY city DESC)<br \/>\nFROM cities<br \/>\nWHERE country = &#8216;New Zealand&#8217;<br \/>\nGROUP BY country;<\/p>\n<p>>> &#8220;New Zealand&#8221;, &#8220;Whangarei, Whanganui, Wellington, Tauranga, Taupo, Rotorua, Palmerston North, New Plymouth, Nelson, Napier, Invercargill, Hamilton, Gisborne, Dunedin, Christchurch, Auckland&#8221;\n<\/p><\/blockquote>\n<p>Note the odd location of ORDER BY, and the absence of a comma between the ORDER BY and the previous argument! See <a href=\"http:\/\/www.postgresql.org\/docs\/9.1\/static\/sql-expressions.html#SYNTAX-AGGREGATES\" target=\"_blank\">Aggregate Expressions<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>One of the things I loved about MySQL was GROUP CONCAT. A very, very useful function. I thought it was something I was going to have to learn to live without having shifted to PostgreSQL. But PostgreSQL has STRING_AGG() (string &hellip; <a href=\"http:\/\/p-s.co.nz\/wordpress\/dont-need-no-stinkin-group_concat\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12],"tags":[],"class_list":["post-1133","post","type-post","status-publish","format-standard","hentry","category-postgresql"],"_links":{"self":[{"href":"http:\/\/p-s.co.nz\/wordpress\/wp-json\/wp\/v2\/posts\/1133"}],"collection":[{"href":"http:\/\/p-s.co.nz\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/p-s.co.nz\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/p-s.co.nz\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/p-s.co.nz\/wordpress\/wp-json\/wp\/v2\/comments?post=1133"}],"version-history":[{"count":6,"href":"http:\/\/p-s.co.nz\/wordpress\/wp-json\/wp\/v2\/posts\/1133\/revisions"}],"predecessor-version":[{"id":1139,"href":"http:\/\/p-s.co.nz\/wordpress\/wp-json\/wp\/v2\/posts\/1133\/revisions\/1139"}],"wp:attachment":[{"href":"http:\/\/p-s.co.nz\/wordpress\/wp-json\/wp\/v2\/media?parent=1133"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/p-s.co.nz\/wordpress\/wp-json\/wp\/v2\/categories?post=1133"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/p-s.co.nz\/wordpress\/wp-json\/wp\/v2\/tags?post=1133"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}