Thread: limiting a select

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.

Any pointers appreciated,
Chris


--

This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed.
If you have received this email in error please notify the sender. Any
offers or quotation of service are subject to formal specification.
Errors and omissions excepted.  Please note that any views or opinions
presented in this email are solely those of the author and do not
necessarily represent those of edNET or lightershade ltd. Finally, the
recipient should check this email and any attachments for the presence of
viruses.  edNET and lightershade ltd accepts no liability for any damage
caused by any virus transmitted by this email.

--
--
Virus scanned by edNET.

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;