Re: limiting a select

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;