From: | Oliver Elphick <olly(at)lfix(dot)co(dot)uk> |
---|---|
To: | Chris Thompson <thompson(at)ednet(dot)co(dot)uk> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: limiting a select |
Date: | 2002-07-05 18:56:34 |
Message-ID: | 1025895395.31483.54.camel@linda |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On Thu, 2002-07-04 at 18:35, Chris Thompson wrote:
>
> Hi,
> I am using the following query
>
> $query = "SELECT id, title, type, SUBSTRING(description FOR $description_size) as description, ";
> $query .= "date, display_date ";
> $query .= "FROM events WHERE active='t' ";
> $query .= "ORDER BY date ASC;";
>
> I was wondering if someone could give me a hint on how to alter this to
> only return 9 results, of which there will, if available, be up to 3 of
> each 'type' of event.
>
> There are 3 possible values for the type column (text) in the db.
You can use the LIMIT keyword to limit results. It sounds as though you
need to use it 3 times, once for each type, and make a union of the 3
selects. I think they have to be subselects to let you use LIMIT on
each one:
SELECT *
FROM (SELECT id, title, type,
SUBSTRING(description FOR $description_size) AS description,
date, display_date
FROM events
WHERE active='t' AND
type = 'X' -- edit this for type 1
ORDER BY date ASC
LIMIT 3) AS x
UNION
SELECT *
FROM (SELECT id, title, type,
SUBSTRING(description FOR $description_size) AS description,
date, display_date
FROM events
WHERE active='t' AND
type = 'Y' -- edit this for type 2
ORDER BY date ASC
LIMIT 3) AS y
UNION
SELECT *
FROM (SELECT id, title, type,
SUBSTRING(description FOR $description_size) AS description,
date, display_date
FROM events
WHERE active='t' AND
type = 'Z' -- edit this for type 3
ORDER BY date ASC
LIMIT 3) AS z
ORDER BY date ASC;
From | Date | Subject | |
---|---|---|---|
Next Message | Terry Yapt | 2002-07-06 10:08:11 | Starting with pl/pgsql.. |
Previous Message | Oliver Elphick | 2002-07-05 18:37:47 | Re: determining Inheritance among tables |