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

Popular posts from this blog

c# - SharpSsh Command Execution -

-
Image
i trying create ssh client using c#. using tamir.sharpssh library. having issues sending command , getting appropriate response server. the way have been testing sending on ls command, followed cd.. command, followed ls command again -- see whether or not cd.. command being executed properly. however, there no difference between first ls command , second ls command. not entirely sure doing wrong. perhaps using wrong ssh type. have provided code , output getting. have added output expect. using tamir.sharpssh; namespace sshnetexample { class program { static void main(string[] args) { sshexec ssh = null; try { console.write("-connecting..."); ssh = new sshexec(host, username, password); ssh.connect(); console.writeline("ok ({0}/{1})", ssh.cipher, ssh.mac); console.writeline("server version={0}, client versio
Read more

python - Specify path of savefig with pylab or matplotlib -

-
i struggling on how find correct way specify save path (or repository) when calling function savefig in matplotlib or pylab. i tried several syntaxes, everytime python console returns : filenotfounderror: [errno 2] no such file or directory: '../mydocs/resource/frames/myimage.png' currently, have written following : pylab.savefig('../mydocs/resource/frames/myimage.png') does know how it? thanks in advance! the tilde operator , variable $home entered strings , therefore not function while saving. have provide either relative path (as did) or provide full path. eg. pylab.savefig("/home/username/desktop/myfig.png") .
Read more

How to run C# code using mono without Xamarin in Android? -

-
i want create program uses mono runtime run c# code (binary not application). i copied libmono.so unity app , i'm write code using documentation: http://www.mono-project.com/docs/advanced/embedding/ and it's working have problems references can access classes mscorlib.dll , have problems system.reflection.assembly class throwing exception, when print console see: system.missingmethodexception: method not found: 'system.reflection.assembly.op_inequality' can fix it? can use mono runtime in c binary on android? ps. i'm using android tests. edit: found solution loading libraries, need set environment variable mono_path libraries path. but have still problems assembly class. the system.missingmethodexception error have seems .net profile mismatch error, 1 explained here . can use mono runtime in c binary on android? yes, using ndk guess. there alternatives can use instead of rolling own, such xmadevlab .
Read more