Sorting with sub-select

[Paul J. Lucas]

  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... ?

[Jack Klebanoff]

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

[Paul J. Lucas]

On Mon, 16 May 2005, Jack Klebanoff wrote:

> Use an ORDER BY clause, e.g.

  But then all the metadata for a single image isn't grouped
  together.

  - Paul

[Stanley Bradbury]

Paul J. Lucas wrote:

> On Mon, 16 May 2005, Jack Klebanoff wrote:
>
>> Use an ORDER BY clause, e.g.
>
>
>     But then all the metadata for a single image isn't grouped
>     together.
>
>     - Paul
>

Hi Paul -
It sounds like a secondary sort on image id would group all the image
metadata together in city order.  Not knowing the cardinality between
the tables (e.g. One-to-One, One-to-Many, etc.) I can't say what he
exact clause should be.  You probably need to specify the foreigh key
column name of each MANY relationship to insure that related items are
grouped.  Assuming relationships of One-to-Many between each record set  
EG:   1 CITY : MANY  image_id : MANY key_id : MANY value_id  I would
recommend:

   ...ORDER BY city, image_id, key_id, value_id