common table expression - PostgreSQL changes result when i play with offset, limit -
hi guys im using below query
if change limit 10 or 20 results different
i'm learning postgresql couldn't figure out problem
there postgresql specific warning ?
recursive children ( select id, name, 1 depth wapi_categories parentid = 1 , visible = true union select a.id, a.name, depth+1 wapi_categories join children b on(a.parentid = b.id) a.visible = true ) select wapi_issues.* (select distinct on(wapi_issues.publicationid) wapi_issues.* wapi_publications right join wapi_category_publication on wapi_category_publication.publication_id = wapi_publications.id right join ( select *, max(wapi_issues.issue_identifier) on (partition wapi_issues.publicationid) max_id wapi_issues wapi_issues.status = true order wapi_issues.issue_identifier desc ) wapi_issues on wapi_issues.publicationid = wapi_category_publication.publication_id , wapi_issues.issue_identifier = max_id right join wapi_issue_files on wapi_issue_files.issueid = wapi_issues.id wapi_publications.status = true , (wapi_category_publication.category_id in ( select id children ) or wapi_category_publication.category_id = 1) , wapi_issue_files.pdf = true) wapi_issues order wapi_issues.issue_identifier desc offset 0 limit 20 wapi_categories ------------------ id, name, parentid ------------------ 1, "root", 0 2, "child 1", 1 3, "child 2", 1 wapi_publication ------------------ id, name, status ------------------ 1, "publication 1", true 2, "publication 2", true wapi_categories_publication ------------------ id, category_id, publication_id ------------------ 1, 2, 1 1, 3, 2 wapi_issues ------------------ id, publicationid, title, issue_identifier, status ------------------ 1, 1, "issue 1", 1, true 2, 1, "issue 2", 3, true 3, 2, "issue 3", 1, true 4, 2, "issue 4", 2, true wapi_issue_files ------------------ id, issueid, file, status ------------------ 1, 1, "file1", true 2, 1, "file2", true 3, 2, "file3", true 4, 2, "file4", true expected result
simply want distinct wapi_publications , last issues sorting wapi_issues.issue_identifier
id, publicationid, title, issue_identifier, status ------------------ 3, 1, "issue 2", 3, true 4, 2, "issue 4", 2, true what mean saying results different
when limit 10;
id, title 1, "a" 2, "b" 3, "c" 4, "d" 5, "e" . . . when limit 20;
id, title 2, "b" 4, "d" 1, "a" 5, "e" 3, "c" . . .
i ended changing order clause this
order wapi_issues.id asc, wapi_issues.issue_identifier desc what understand postgresql needs explicit sortings
Comments
Post a Comment