{"action":"created","issue":{"url":"https://api.github.com/repos/yiisoft/yii2/issues/11932","repository_url":"https://api.github.com/repos/yiisoft/yii2","labels_url":"https://api.github.com/repos/yiisoft/yii2/issues/11932/labels{/name}","comments_url":"https://api.github.com/repos/yiisoft/yii2/issues/11932/comments","events_url":"https://api.github.com/repos/yiisoft/yii2/issues/11932/events","html_url":"https://github.com/yiisoft/yii2/issues/11932","id":164852175,"number":11932,"title":"limit() and distinct() doesn't work together in ActiveQuery ","user":{"login":"Clyff","id":7630386,"avatar_url":"https://avatars.githubusercontent.com/u/7630386?v=3","gravatar_id":"","url":"https://api.github.com/users/Clyff","html_url":"https://github.com/Clyff","followers_url":"https://api.github.com/users/Clyff/followers","following_url":"https://api.github.com/users/Clyff/following{/other_user}","gists_url":"https://api.github.com/users/Clyff/gists{/gist_id}","starred_url":"https://api.github.com/users/Clyff/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Clyff/subscriptions","organizations_url":"https://api.github.com/users/Clyff/orgs","repos_url":"https://api.github.com/users/Clyff/repos","events_url":"https://api.github.com/users/Clyff/events{/privacy}","received_events_url":"https://api.github.com/users/Clyff/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/yiisoft/yii2/labels/MSSQL","name":"MSSQL","color":"006b75"},{"url":"https://api.github.com/repos/yiisoft/yii2/labels/status:to%20be%20verified","name":"status:to be verified","color":"fbca04"}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":{"url":"https://api.github.com/repos/yiisoft/yii2/milestones/9","html_url":"https://github.com/yiisoft/yii2/milestones/2.0.x","labels_url":"https://api.github.com/repos/yiisoft/yii2/milestones/9/labels","id":831720,"number":9,"title":"2.0.x","description":"","creator":{"login":"cebe","id":189796,"avatar_url":"https://avatars.githubusercontent.com/u/189796?v=3","gravatar_id":"","url":"https://api.github.com/users/cebe","html_url":"https://github.com/cebe","followers_url":"https://api.github.com/users/cebe/followers","following_url":"https://api.github.com/users/cebe/following{/other_user}","gists_url":"https://api.github.com/users/cebe/gists{/gist_id}","starred_url":"https://api.github.com/users/cebe/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/cebe/subscriptions","organizations_url":"https://api.github.com/users/cebe/orgs","repos_url":"https://api.github.com/users/cebe/repos","events_url":"https://api.github.com/users/cebe/events{/privacy}","received_events_url":"https://api.github.com/users/cebe/received_events","type":"User","site_admin":false},"open_issues":431,"closed_issues":2,"state":"open","created_at":"2014-10-19T13:49:45Z","updated_at":"2016-08-22T09:08:17Z","due_on":null,"closed_at":null},"comments":7,"created_at":"2016-07-11T14:33:52Z","updated_at":"2016-08-22T12:09:44Z","closed_at":null,"body":"### What steps will reproduce the problem?\r\n\r\nWith any model:\r\n\r\n```php\r\n$query = Model::find()->select('attribute')->distinct()->limit(20)->orderBy('attribute');\r\n```\r\n\r\n### What is the expected result?\r\n\r\nA query that searches for 20 distinct values of `attribute` and returns it.\r\n\r\n### What do you get instead?\r\n\r\nRepeated values are being returned in my result. The reason is because `$query->createCommand()->getRawSql()` returns:\r\n\r\n```sql\r\nSELECT TOP 20 * FROM (SELECT DISTINCT rowNum = ROW_NUMBER() over (ORDER BY [attribute]), [attribute] FROM [Model]) sub\r\n```\r\n\r\nMaking the results look like this:\r\n\r\n| rowNum                | attribute\r\n| ---------------- | ---\r\n| 1      | value1\r\n| 2      | value1\r\n| 3      | value2\r\n| 4      | value3\r\n\r\n\r\nI didn't had time to look at the code and make a sugestion, sorry. \r\n\r\nAnd I found a similar issue [here](https://github.com/yiisoft/yii2/issues/8732), not sure if we are talking about the same problem (I'm using mysql)"},"comment":{"url":"https://api.github.com/repos/yiisoft/yii2/issues/comments/241393741","html_url":"https://github.com/yiisoft/yii2/issues/11932#issuecomment-241393741","issue_url":"https://api.github.com/repos/yiisoft/yii2/issues/11932","id":241393741,"user":{"login":"Patricy","id":4677661,"avatar_url":"https://avatars.githubusercontent.com/u/4677661?v=3","gravatar_id":"","url":"https://api.github.com/users/Patricy","html_url":"https://github.com/Patricy","followers_url":"https://api.github.com/users/Patricy/followers","following_url":"https://api.github.com/users/Patricy/following{/other_user}","gists_url":"https://api.github.com/users/Patricy/gists{/gist_id}","starred_url":"https://api.github.com/users/Patricy/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Patricy/subscriptions","organizations_url":"https://api.github.com/users/Patricy/orgs","repos_url":"https://api.github.com/users/Patricy/repos","events_url":"https://api.github.com/users/Patricy/events{/privacy}","received_events_url":"https://api.github.com/users/Patricy/received_events","type":"User","site_admin":false},"created_at":"2016-08-22T12:09:44Z","updated_at":"2016-08-22T12:09:44Z","body":" This happens only for old version MSSQL Servers.\r\nyii\\db\\mssql\\QueryBuilder\r\nMethod **oldBuildOrderByAndLimit**\r\n\r\nThis line causes problem:\r\n`$sql = preg_replace('/^([\\s(])*SELECT(\\s+DISTINCT)?(?!\\s*TOP\\s*\\()/i', \"\\\\1SELECT\\\\2 rowNum = ROW_NUMBER() over ($orderBy),\", $sql);`\r\n\r\n It replaces        \r\n`SELECT DISTINCT TOP  2  [attribute] FROM [tablename]`\r\nwith query pointed in head of this thread.\r\n\r\nI think, it should return \r\n`SELECT TOP 20  [attribute] FROM (SELECT DISTINCT rowNum = ROW_NUMBER() over (ORDER BY [attribute]), [attribute] FROM [Model]) sub`\r\n\r\n\r\n\r\nI have newer MSSQL version on my project so I use other method of QueryBuilder and  I can just simulate.\r\nIf you guys confirm it works (I mean last query), I can play around this replacement,\r\nExact list of fields should be selected instead of *"}}
{"actor":{"display_login":"Patricy"}}