[SQL] ordering of selected rows from an ordered subselect
Hi, we know that rows in a table are not stored in any particular order and explicit order by clause is required to get data in any particular order. but does it apply to select queries from ordered subselects also ? eg select id , name , expensive_func(name) from ( select id , name from tab order by c1 desc limit 15) as foo ; is it guaranteed that the final result is order by c1 ? Regds mallah. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] sub-limiting a query
Hello, I've got a table of shows with different types (movie, theater, ballet,etc.) and I am looking for a select that can return the 10 last entered shows AND at most 2 of each type. Is that possible in one query? The table looks basically like: created_on | timestamp without time zone show_name | text id_show | integer show_type | text id_show_subtype | integer ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] sub-limiting a query
am Sat, dem 17.02.2007, um 13:56:35 +0100 mailte Louis-David Mitterrand folgendes: > Hello, > > I've got a table of shows with different types (movie, theater, > ballet,etc.) and I am looking for a select that can return the 10 last > entered shows AND at most 2 of each type. Is that possible in one query? > > The table looks basically like: > > created_on | timestamp without time zone > show_name | text > id_show | integer > show_type | text > id_show_subtype | integer > You can try to divide this into 2 selects: First, select the last 10 entered show, this is simple. Then, write a stored proc. Within, select for every show_type the 2 last events. Both results combine with UNION ALL. I know, the hard part is the function. I havn't time at the moment to write an example, we have guests... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] ordering of selected rows from an ordered subselect
On Sat, Feb 17, 2007 at 03:02:06PM +0530, Rajesh Kumar Mallah wrote: > > select id , name , expensive_func(name) from >( select id , name from tab order by c1 desc limit 15) as foo ; > > is it guaranteed that the final result is order by c1 ? No, because expensive_func(name) might do something that alters it, no? All things considered, it's a pretty good _bet_ it will be ordered as you wish, though. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] ordering of selected rows from an ordered subselect
"Rajesh Kumar Mallah" <[EMAIL PROTECTED]> writes: > select id , name , expensive_func(name) from > ( select id , name from tab order by c1 desc limit 15) as foo ; > is it guaranteed that the final result is order by c1 ? The sub-select's output will be emitted in the specified order. What happens after that depends on the outer query, but if you don't have any joining or grouping then it's a reasonably safe bet that the final output will be in the same order. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] sub-limiting a query
Louis-David Mitterrand wrote: Hello, I've got a table of shows with different types (movie, theater, ballet,etc.) and I am looking for a select that can return the 10 last entered shows AND at most 2 of each type. Is that possible in one query? The table looks basically like: created_on | timestamp without time zone show_name | text id_show | integer show_type | text id_show_subtype | integer ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster Looks like a top n query and whilst there are many top-n query solution I'd refer you to a Joe Celko solution. Check the "top salesperson contest" and I think you should be able to work your problem (http://www.dbmsmag.com/9610d06.html) Mario ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] sub-limiting a query
Louis-David Mitterrand <[EMAIL PROTECTED]> schrieb: > ballet,etc.) and I am looking for a select that can return the 10 last > entered shows AND at most 2 of each type. Is that possible in one query? A similar question i found in the archive and there are a couple of answers: (for the second part of your question) http://archives.postgresql.org/pgsql-sql/2005-03/msg00408.php But as i said, there is no plain sql-solution. Unfortunately we haven't windowing functions, but perhaps in the future, Gavin Sharry is working on this. Take a look at http://www.gavinsherry.org/blog/index.php?/archives/7-Window-function-presentation.html Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] sub-limiting a query
Louis-David Mitterrand wrote: Hello, I've got a table of shows with different types (movie, theater, ballet,etc.) and I am looking for a select that can return the 10 last entered shows AND at most 2 of each type. Is that possible in one query? The table looks basically like: created_on | timestamp without time zone show_name | text id_show | integer show_type | text id_show_subtype | integer ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster I thought of another solution, actually it's of those top n query tricks that I picked up somewhere, can't remember where. Assuming that your table is called shows, the following query should give you the results you want (hope so) SELECT * FROM shows a WHERE 3 > (SELECT COUNT(*) FROM shows b WHERE b.created_on >= a.created_on and a.show_type = b.show_type) Mario ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] sub-limiting a query
On Sat, Feb 17, 2007 at 07:21:40PM +0100, M.P.Dankoor wrote: > Louis-David Mitterrand wrote: > > > I thought of another solution, actually it's of those top n query tricks > that I picked up somewhere, can't remember > where. > Assuming that your table is called shows, the following query should > give you the results you want (hope so) > > SELECT * > FROM shows a > WHERE 3 > (SELECT COUNT(*) > FROM shows b > WHERE b.created_on >= a.created_on > and a.show_type = b.show_type) This is stunning and it works! I can barely understand the query: it's so terse it hurts :) /me goes back studying it Thanks a lot! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Odd PL/PgSQL Error -- relation "X" does not exist when using index expression
Michael Fuhr wrote: > On Thu, Feb 08, 2007 at 10:32:25AM -0500, Tom Lane wrote: > > My advice is not to try to execute multiple commands in the same EXECUTE > > string --- if we were going to do anything to "fix" this, I think it > > would be along the lines of enforcing that advice. Trying to make the > > world safe for it doesn't sound productive. > > The SPI_execute() documentation does mention that multiple commands > are allowed: > > http://www.postgresql.org/docs/8.2/interactive/spi-spi-execute.html > > "You may pass multiple commands in one string. SPI_execute returns > the result for the command executed last. The count limit applies > to each command separately, but it is not applied to hidden commands > generated by rules. > > "When read_only is false, SPI_execute increments the command counter > and computes a new snapshot before executing each command in the > string." > > Should that documentation be modified? Done, and attached. -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/spi.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/spi.sgml,v retrieving revision 1.52 diff -c -c -r1.52 spi.sgml *** doc/src/sgml/spi.sgml 1 Feb 2007 19:10:24 - 1.52 --- doc/src/sgml/spi.sgml 18 Feb 2007 01:45:45 - *** *** 321,327 !You can pass multiple commands in one string. SPI_execute returns the result for the command executed last. The count limit applies to each command separately, but it is not applied to --- 321,328 !You can pass multiple commands in one string, but later commands cannot !depend on the creation of objects earlier in the string. SPI_execute returns the result for the command executed last. The count limit applies to each command separately, but it is not applied to ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
