Thread: returning only part of a rule set
I have a psql function that make a general sql query, returns a set of results Q. Is there anyway that I can limit the size of the result sets ? Dave.
Dave Potts wrote > I have a psql function that make a general sql query, returns a set of > results > Q. Is there anyway that I can limit the size of the result sets ? SELECT * FROM somewhere LIMIT 50; <--- David J. btw: what is a "psql function"? -- View this message in context: http://postgresql.nabble.com/returning-only-part-of-a-rule-set-tp5828217p5828218.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 11/25/2014 01:27 PM, Dave Potts wrote: > I have a psql function that make a general sql query, returns a set of > results > > > Q. Is there anyway that I can limit the size of the result sets ? Put a LIMIT on the query. If that is not what you want then we will need to see the code and/or get a more detailed explanation of where you want the limiting to occur. > > Dave. > > > > > > > -- Adrian Klaver adrian.klaver@aklaver.com
On 25/11/14 21:36, Adrian Klaver wrote: > On 11/25/2014 01:27 PM, Dave Potts wrote: >> I have a psql function that make a general sql query, returns a set of >> results >> >> >> Q. Is there anyway that I can limit the size of the result sets ? > > Put a LIMIT on the query. > > If that is not what you want then we will need to see the code and/or > get a more detailed explanation of where you want the limiting to occur. I tried that Limit applies to the total number of results from a query, what I want to do is limit the total number of returns per result set, For example if my query returns a set of items such as id, foo,bar Normally the result set is 3 set of records one with 3 entries, one with 2 entires and one with entry, the results would be. 1 xx,yy 2 xx,yy 3 xx,yy 1,dd,zz 2,dd,zz 1, ee,ff If I am only interested in get 2 entries per result set, I would expect to see 1 xx,yy 2 xx,yy 1,dd,zz 2,dd,zz 1, ee,ff Using LIMIT only gives 1 xx,yy 2 xx,yy Sorry for not explaining it very, I want to limit the size of an inviduail set of records which is part of set of records. > >> >> Dave. >> >> >> >> >> >> >> > >
On 11/25/2014 01:56 PM, Dave Potts wrote: > On 25/11/14 21:36, Adrian Klaver wrote: >> On 11/25/2014 01:27 PM, Dave Potts wrote: >>> I have a psql function that make a general sql query, returns a set of >>> results >>> >>> >>> Q. Is there anyway that I can limit the size of the result sets ? >> >> Put a LIMIT on the query. >> >> If that is not what you want then we will need to see the code and/or >> get a more detailed explanation of where you want the limiting to occur. > I tried that Limit applies to the total number of results from a > query, what I want to do is limit the total number of returns per > result set, > > For example if my query returns a set of items such as id, foo,bar > > Normally the result set is 3 set of records one with 3 entries, one with > 2 entires and one with entry, the results would be. > > 1 xx,yy > 2 xx,yy > 3 xx,yy > > 1,dd,zz > 2,dd,zz > > 1, ee,ff So you are selecting on foo and bar? > > If I am only interested in get 2 entries per result set, I would expect > to see Actually <=2. How do you determine which rows to keep, by id or something else? > > 1 xx,yy > 2 xx,yy > > > 1,dd,zz > 2,dd,zz > > 1, ee,ff > > Using LIMIT only gives > 1 xx,yy > 2 xx,yy > > Sorry for not explaining it very, I want to limit the size of an > inviduail set of records which is part of set of records. It would help if we could see the actual query you are using to get the result sets, suitably anonymized if needed. > > > > >> >>> >>> Dave. >>> >>> >>> >>> >>> >>> >>> >> >> > > > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver-4 wrote >> >> If I am only interested in get 2 entries per result set, I would expect >> to see > > Actually <=2. > How do you determine which rows to keep, by id or something else? > >> >> 1 xx,yy >> 2 xx,yy >> >> >> 1,dd,zz >> 2,dd,zz >> >> 1, ee,ff >> >> Using LIMIT only gives >> 1 xx,yy >> 2 xx,yy >> >> Sorry for not explaining it very, I want to limit the size of an >> inviduail set of records which is part of set of records. > > It would help if we could see the actual query you are using to get the > result sets, suitably anonymized if needed. The general answer is that you use a window clause and a row_number() function over an appropriate partiton. Put that in a subquery then in the outer query add a where clause for row_number <= 2. David J. -- View this message in context: http://postgresql.nabble.com/returning-only-part-of-a-rule-set-tp5828217p5828226.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 25/11/14 22:24, David G Johnston wrote: Thanks List, I think this is the right way to go. > Adrian Klaver-4 wrote >>> If I am only interested in get 2 entries per result set, I would expect >>> to see >> Actually <=2. >> How do you determine which rows to keep, by id or something else? >> >>> 1 xx,yy >>> 2 xx,yy >>> >>> >>> 1,dd,zz >>> 2,dd,zz >>> >>> 1, ee,ff >>> >>> Using LIMIT only gives >>> 1 xx,yy >>> 2 xx,yy >>> >>> Sorry for not explaining it very, I want to limit the size of an >>> inviduail set of records which is part of set of records. >> It would help if we could see the actual query you are using to get the >> result sets, suitably anonymized if needed. > The general answer is that you use a window clause and a row_number() > function over an appropriate partiton. Put that in a subquery then in the > outer query add a where clause for row_number <= 2. > > David J. > > > > > > > -- > View this message in context: http://postgresql.nabble.com/returning-only-part-of-a-rule-set-tp5828217p5828226.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > >