db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jack Klebanoff <klebanoff-de...@sbcglobal.net>
Subject Re: Sorting with sub-select
Date Mon, 16 May 2005 20:50:04 GMT
Paul J. Lucas wrote:

>     Sorry if this is too-general an SQL question, but suppose I
>     have the following tables:
>
>         CREATE TABLE image (
>             image_id       INT NOT NULL PRIMARY KEY
>                            GENERATED ALWAYS AS IDENTITY,
>             file_name      VARCHAR(64) NOT NULL
>         );
>
>         CREATE TABLE md_key (
>             key_id         INT NOT NULL PRIMARY KEY
>                            GENERATED ALWAYS AS IDENTITY,
>             image_id       INT NOT NULL REFERENCES image,
>             tag            VARCHAR(20)
>         );
>
>         CREATE TABLE md_value (
>             value_id       INT NOT NULL PRIMARY KEY
>                            GENERATED ALWAYS AS IDENTITY,
>             key_id         INT NOT NULL REFERENCES md_key,
>             value          VARCHAR(255) NOT NULL
>         );
>
>     That is a set of images; for each image, a set of keys; for each
>     key, a set of values.  For an image, its set of keys and values
>     comprise its metadata.  Every image can have a different set of
>     keys and values.
>
>     If I want to select all the metadata for a set of images that
>     actually have a metadata key of 'City', I can use a sub-select
>     like:
>
>         SELECT i.file_name, k.tag, v.value
>           FROM image i, md_key k, md_value v
>          WHERE i.image_id = k.image_id
>            AND k.key_id = v.key_id
>            AND i.image_id IN
>             (
>             SELECT i.image_id
>               FROM image i, md_key k, md_value v
>              WHERE i.image_id = k.image_id
>                AND k.key_id = v.key_id
>                AND k.tag = 'City'
>             )
>
>     My question is: is there any way to sort the results by the
>     value of City (v.value)?
>
>     - Paul
>
>     P.S.: Is there a better place to post general SQL questions?
>     comp.databases... ?
>
Use an ORDER BY clause, e.g.

        SELECT i.file_name, k.tag, v.value
          FROM image i, md_key k, md_value v
         WHERE i.image_id = k.image_id
           AND k.key_id = v.key_id
           AND i.image_id IN
            (
            SELECT i.image_id
              FROM image i, md_key k, md_value v
             WHERE i.image_id = k.image_id
               AND k.key_id = v.key_id
               AND k.tag = 'City'
            )
          ORDER BY v.value

Jack Klebanoff

Mime
View raw message