{"action":"created","issue":{"url":"https://api.github.com/repos/hadley/dplyr/issues/2340","repository_url":"https://api.github.com/repos/hadley/dplyr","labels_url":"https://api.github.com/repos/hadley/dplyr/issues/2340/labels{/name}","comments_url":"https://api.github.com/repos/hadley/dplyr/issues/2340/comments","events_url":"https://api.github.com/repos/hadley/dplyr/issues/2340/events","html_url":"https://github.com/hadley/dplyr/issues/2340","id":197270583,"number":2340,"title":"Feature request: use \"distinct on\" from Posgres to allow keeping all columns","user":{"login":"karldw","id":12504708,"avatar_url":"https://avatars.githubusercontent.com/u/12504708?v=3","gravatar_id":"","url":"https://api.github.com/users/karldw","html_url":"https://github.com/karldw","followers_url":"https://api.github.com/users/karldw/followers","following_url":"https://api.github.com/users/karldw/following{/other_user}","gists_url":"https://api.github.com/users/karldw/gists{/gist_id}","starred_url":"https://api.github.com/users/karldw/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/karldw/subscriptions","organizations_url":"https://api.github.com/users/karldw/orgs","repos_url":"https://api.github.com/users/karldw/repos","events_url":"https://api.github.com/users/karldw/events{/privacy}","received_events_url":"https://api.github.com/users/karldw/received_events","type":"User","site_admin":false},"labels":[{"id":213548409,"url":"https://api.github.com/repos/hadley/dplyr/labels/database","name":"database","color":"eeeeee","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2016-12-22T21:31:13Z","updated_at":"2016-12-24T13:08:20Z","closed_at":null,"body":"Currently, `distinct(..., .keep_all=TRUE)` only works with local data because standard SQL doesn't know how to look at distinct values of a subset of columns.\r\nHowever, [Postgres does](https://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT), with the `DISTINCT ON` syntax, so it should be possible for the postgres SQL generation to allow `.keep_all=TRUE`.\r\n\r\nExamples:\r\n```r\r\nlibrary(dplyr)\r\nmtcars2 <- copy_to(src_postgres(), mtcars)\r\n\r\ndistinct(mtcars,  cyl, .keep_all=TRUE)  # This works\r\n\r\ndistinct(mtcars2, cyl, .keep_all=TRUE)  # This could work, but doesn't\r\n\r\nError: Can't calculate distinct only on specified columns with SQL unless .keep_all is FALSE\r\n```\r\n \r\n"},"comment":{"url":"https://api.github.com/repos/hadley/dplyr/issues/comments/269083747","html_url":"https://github.com/hadley/dplyr/issues/2340#issuecomment-269083747","issue_url":"https://api.github.com/repos/hadley/dplyr/issues/2340","id":269083747,"user":{"login":"karldw","id":12504708,"avatar_url":"https://avatars.githubusercontent.com/u/12504708?v=3","gravatar_id":"","url":"https://api.github.com/users/karldw","html_url":"https://github.com/karldw","followers_url":"https://api.github.com/users/karldw/followers","following_url":"https://api.github.com/users/karldw/following{/other_user}","gists_url":"https://api.github.com/users/karldw/gists{/gist_id}","starred_url":"https://api.github.com/users/karldw/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/karldw/subscriptions","organizations_url":"https://api.github.com/users/karldw/orgs","repos_url":"https://api.github.com/users/karldw/repos","events_url":"https://api.github.com/users/karldw/events{/privacy}","received_events_url":"https://api.github.com/users/karldw/received_events","type":"User","site_admin":false},"created_at":"2016-12-24T13:08:20Z","updated_at":"2016-12-24T13:08:20Z","body":"Yes, that looks like the right syntax.\r\n\r\nI don't know any other DBMS that have the `DISTINCT ON` syntax, but it looks like the same thing can be accomplished (for MySQL and SQLite) with a `GROUP BY` and including non-aggregating columns in the select. \r\n\r\n#### Non-aggregating group by\r\n```sql\r\nSELECT * FROM mtcars GROUP BY cyl\r\n```\r\n (This is non-standard SQL; [some people on the internet are unhappy about it](http://stackoverflow.com/a/3168601).)\r\n\r\nI'm less sure about other DBMS, but if there are any that would be particularly worth testing, let me know.\r\n\r\n<details>\r\n\r\nHere are some examples, stolen from [here](https://bernardoamc.github.io/sql/2015/05/04/group-by-non-aggregate-columns/).\r\n### MySQL example\r\n\r\n```sql\r\nCREATE TABLE games (\r\n  game_id int AUTO_INCREMENT ,\r\n  name VARCHAR(18),\r\n  price INT,\r\n  released_at DATE,\r\n  publisher VARCHAR(14),\r\n  PRIMARY KEY (game_id)\r\n);\r\n\r\nINSERT INTO games (name, price, released_at, publisher) VALUES\r\n  ('Metal Slug Defense', 30, '2015-05-01', 'SNK Playmore'),\r\n  ('Project Druid', 20, '2015-05-01', 'shortcircuit'),\r\n  ('Chroma Squad', 40, '2015-04-30', 'Behold Studios'),\r\n  ('Soul Locus', 30, '2015-04-30', 'Fat Loot Games'),\r\n  ('Subterrain', 40, '2015-04-30', 'Pixellore');\r\n\r\nSELECT * FROM games GROUP BY released_at;\r\n```\r\n\r\n### SQLite example\r\n\r\n```sql\r\n\r\nCREATE TABLE games (\r\n  name VARCHAR,\r\n  price BIGINT,\r\n  released_at DATE,\r\n  publisher TEXT\r\n);\r\n\r\nINSERT INTO games (name, price, released_at, publisher) VALUES\r\n  ('Metal Slug Defense', 30, '2015-05-01', 'SNK Playmore'),\r\n  ('Project Druid', 20, '2015-05-01', 'shortcircuit'),\r\n  ('Chroma Squad', 40, '2015-04-30', 'Behold Studios'),\r\n  ('Soul Locus', 30, '2015-04-30', 'Fat Loot Games'),\r\n  ('Subterrain', 40, '2015-04-30', 'Pixellore');\r\n\r\nSELECT rowid AS game_id, * FROM games GROUP BY released_at;\r\n\r\n```\r\n\r\n\r\n### PostgreSQL example\r\n\r\n```sql\r\nCREATE TABLE games (\r\n  game_id serial PRIMARY KEY,\r\n  name VARCHAR,\r\n  price BIGINT,\r\n  released_at DATE,\r\n  publisher TEXT\r\n);\r\n\r\nINSERT INTO games (name, price, released_at, publisher) VALUES\r\n  ('Metal Slug Defense', 30, '2015-05-01', 'SNK Playmore'),\r\n  ('Project Druid', 20, '2015-05-01', 'shortcircuit'),\r\n  ('Chroma Squad', 40, '2015-04-30', 'Behold Studios'),\r\n  ('Soul Locus', 30, '2015-04-30', 'Fat Loot Games'),\r\n  ('Subterrain', 40, '2015-04-30', 'Pixellore');\r\n\r\n\r\nSELECT DISTINCT ON (released_at) * FROM games;\r\n```\r\n\r\n</details>"}}
{"actor":{"display_login":"karldw"}}