pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsub-limiting a query
6+ messages / 4 participants
[nested] [flat]
* sub-limiting a query
@ 2007-02-17 12:56 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-02-17 15:00 ` Re: sub-limiting a query A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-02-17 17:35 ` Re: sub-limiting a query M.P.Dankoor <m.p.dankoor@gmail.com>
2007-02-17 17:47 ` Re: sub-limiting a query Andreas Kretschmer <akretschmer@spamfence.net>
2007-02-17 18:21 ` Re: sub-limiting a query M.P.Dankoor <m.p.dankoor@gmail.com>
0 siblings, 4 replies; 6+ messages in thread
From: Louis-David Mitterrand @ 2007-02-17 12:56 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: sub-limiting a query
2007-02-17 12:56 sub-limiting a query Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
@ 2007-02-17 15:00 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
3 siblings, 0 replies; 6+ messages in thread
From: A. Kretschmer @ 2007-02-17 15:00 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: sub-limiting a query
2007-02-17 12:56 sub-limiting a query Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
@ 2007-02-17 17:35 ` M.P.Dankoor <m.p.dankoor@gmail.com>
3 siblings, 0 replies; 6+ messages in thread
From: M.P.Dankoor @ 2007-02-17 17:35 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: sub-limiting a query
2007-02-17 12:56 sub-limiting a query Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
@ 2007-02-17 17:47 ` Andreas Kretschmer <akretschmer@spamfence.net>
3 siblings, 0 replies; 6+ messages in thread
From: Andreas Kretschmer @ 2007-02-17 17:47 UTC (permalink / raw)
To: pgsql-sql
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°
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: sub-limiting a query
2007-02-17 12:56 sub-limiting a query Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
@ 2007-02-17 18:21 ` M.P.Dankoor <m.p.dankoor@gmail.com>
2007-02-17 18:40 ` Re: sub-limiting a query Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
3 siblings, 1 reply; 6+ messages in thread
From: M.P.Dankoor @ 2007-02-17 18:21 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: sub-limiting a query
2007-02-17 12:56 sub-limiting a query Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-02-17 18:21 ` Re: sub-limiting a query M.P.Dankoor <m.p.dankoor@gmail.com>
@ 2007-02-17 18:40 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 0 replies; 6+ messages in thread
From: Louis-David Mitterrand @ 2007-02-17 18:40 UTC (permalink / raw)
To: pgsql-sql
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!
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-02-17 18:40 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-17 12:56 sub-limiting a query Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2007-02-17 15:00 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-02-17 17:35 ` M.P.Dankoor <m.p.dankoor@gmail.com>
2007-02-17 17:47 ` Andreas Kretschmer <akretschmer@spamfence.net>
2007-02-17 18:21 ` M.P.Dankoor <m.p.dankoor@gmail.com>
2007-02-17 18:40 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox