Thread: trivial problem
this sounds as if there must be a rather trivial solution: i am looking for the 50 rows with the largest value for a particular column. i could create a view with create view as select * from table order by column; and then somehow only retrieve the top 50 rows . . . but how? thanks frank
select * from table order by column limit 50 Oleg On Fri, 29 Oct 1999, Frank Joerdens wrote: > Date: Fri, 29 Oct 1999 14:55:29 +0200 > From: Frank Joerdens <frank@x9media.com> > To: pgsql-sql@postgreSQL.org > Subject: [SQL] trivial problem > > this sounds as if there must be a rather trivial solution: > > i am looking for the 50 rows with the largest value for a particular > column. i could create a view with > > create view as select * from table order by column; > > and then somehow only retrieve the top 50 rows . . . but how? > > thanks > > frank > > ************ > _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
On Fri, Oct 29, 1999 at 02:55:29PM +0200, Frank Joerdens allegedly wrote: > this sounds as if there must be a rather trivial solution: > > i am looking for the 50 rows with the largest value for a particular > column. i could create a view with > > create view as select * from table order by column; > > and then somehow only retrieve the top 50 rows . . . but how? > > thanks > > frank > > ************ Use limit? Mathijs
On Fri, 29 Oct 1999, Oleg Bartunov wrote: > select * from table order by column limit 50 > It used to be and could still be in Oracle (sorry to pull them into this) that limit applied to the selection clause so the first 'n' tulples selected,not the top/bottom 'n', would be reported. Would this be a feature of the SQL standard or of Oracles implementation? And how does PostgreSQL handle limits? Rod -- Roderick A. Anderson raanders@altoplanos.net Altoplanos Information Systems, Inc. Voice: 208.765.6149 212 S. 11th Street, Suite 5 FAX: 208.664.5299 Coeur d'Alene, ID 83814
If limit didn't mean "get first 50," there would be no time/effort saved by specifying a limit. I.e. when a limit is specified, the backend looks for matches until it finds 50 records, and then stops. Troy > > On Fri, 29 Oct 1999, Oleg Bartunov wrote: > > > select * from table order by column limit 50 > > > > It used to be and could still be in Oracle (sorry to pull them into this) > that limit applied to the selection clause so the first 'n' tulples > selected,not the top/bottom 'n', would be reported. > Would this be a feature of the SQL standard or of Oracles > implementation? And how does PostgreSQL handle limits? > > Rod > -- > Roderick A. Anderson > raanders@altoplanos.net Altoplanos Information Systems, Inc. > Voice: 208.765.6149 212 S. 11th Street, Suite 5 > FAX: 208.664.5299 Coeur d'Alene, ID 83814 > > > ************ > >
----- Original Message ----- From: <tjk@tksoft.com> To: Roderick A. Anderson <raanders@altoplanos.net> Cc: <oleg@sai.msu.su>; <pgsql-sql@postgreSQL.org> Sent: Friday, October 29, 1999 7:12 PM Subject: Re: [SQL] trivial problem > If limit didn't mean "get first 50," there would be no time/effort > saved by specifying a limit. > > I.e. when a limit is specified, the backend looks for matches until it > finds 50 records, and then stops. Eh? I think the questioner meant effectively "in a query with LIMIT and ORDER BY, which takes precedence?". To which the answer appears to be ORDER BY, which surprised me, but is of course very useful. I.e. all sorting is completed, and then the first fifty records are returned. Does that mean that the seek time for a limited, ordered by query is the same as that for an unlimited query? The extra speed gained from the limit only achieved by not having to return the data? Or is there some kind of magic going on to allow it to ignore certain things about the query when a LIMIT is in place?
FAQ says: See the <I>fetch</I> manual page, or use SELECT ... LIMIT....<P> This only prevents all row results from being transferred to the client. The entire query must be evaluated, even if you only want just the first few rows. Consider a query that has an <I>order by.</I> There is no way to return any rows until the entire query is evaluated and sorted.<P> [Charset iso-8859-1 unsupported, filtering to ASCII...] > > ----- Original Message ----- > From: <tjk@tksoft.com> > To: Roderick A. Anderson <raanders@altoplanos.net> > Cc: <oleg@sai.msu.su>; <pgsql-sql@postgreSQL.org> > Sent: Friday, October 29, 1999 7:12 PM > Subject: Re: [SQL] trivial problem > > > > If limit didn't mean "get first 50," there would be no time/effort > > saved by specifying a limit. > > > > I.e. when a limit is specified, the backend looks for matches until it > > finds 50 records, and then stops. > > Eh? I think the questioner meant effectively "in a query with LIMIT and > ORDER BY, which takes precedence?". To which the answer appears to be ORDER > BY, which surprised me, but is of course very useful. > > I.e. all sorting is completed, and then the first fifty records are > returned. > > Does that mean that the seek time for a limited, ordered by query is the > same as that for an unlimited query? The extra speed gained from the limit > only achieved by not having to return the data? Or is there some kind of > magic going on to allow it to ignore certain things about the query when a > LIMIT is in place? > > > ************ > > -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian <maillist@candle.pha.pa.us> writes:
> FAQ says:
> See the <I>fetch</I> manual page, or use SELECT ... LIMIT....<P>
> This only prevents all row results from being transferred to the client.
> The entire query must be evaluated, even if you only want just the first
> few rows. Consider a query that has an <I>order by.</I> There is no way
> to return any rows until the entire query is evaluated and sorted.<P>
That FAQ entry is not right. It might be right for FETCH, but not for
SELECT ... LIMIT. With a LIMIT, the executor will stop once it has
generated the requested number of rows. Of course, how much computation
is needed to reach that point depends greatly on the query and the query
plan.
If an explicit sort step is being used to implement ORDER BY, then the
FAQ is correct. But if the ORDER BY is implemented by an index scan,
rather than an explicit sort step, then presto: we just run the
indexscan for the first N tuples and stop. (6.5.* is not very bright
about avoiding a sort step, but current sources are much better.)
Still to be done: modify the optimizer to be aware of this fact so that
it will be more likely to choose an indexscan when a small LIMIT is
present. Right now it chooses indexscan vs. explicit sort on the basis
of cost to return all the tuples, which is bogus if there's a LIMIT.
Ideally it'd also take LIMIT into account when choosing join types,
etc...
regards, tom lane
> Bruce Momjian <maillist@candle.pha.pa.us> writes: > > FAQ says: > > See the <I>fetch</I> manual page, or use SELECT ... LIMIT....<P> > > > This only prevents all row results from being transferred to the client. > > The entire query must be evaluated, even if you only want just the first > > few rows. Consider a query that has an <I>order by.</I> There is no way > > to return any rows until the entire query is evaluated and sorted.<P> > > That FAQ entry is not right. It might be right for FETCH, but not for > SELECT ... LIMIT. With a LIMIT, the executor will stop once it has > generated the requested number of rows. Of course, how much computation > is needed to reach that point depends greatly on the query and the query > plan. New FAQ text is: The entire query may have to be evaluated, even if you only want the first few rows. Consider a query that has an ORDERBY. If there is an index that matches the ORDER BY, PostgreSQL may be able to evaluate only the first few recordsrequested, or the entire query may have to be evaluated until the desired rows have been generated. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026