You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by "Paul J. Lucas" <pa...@mac.com> on 2005/05/16 17:46:42 UTC
Sorting with sub-select
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... ?
Re: Sorting with sub-select
Posted by Stanley Bradbury <St...@gmail.com>.
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
Re: Sorting with sub-select
Posted by "Paul J. Lucas" <pa...@mac.com>.
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
Re: Sorting with sub-select
Posted by Jack Klebanoff <kl...@sbcglobal.net>.
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