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;