Thread: 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
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
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
Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org> 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°
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
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!