Re: How do I order by some of the rows in a column. Not all.

In the example given, order by PP.VALUE because it contains the title after all. If you need to restrict or order by multiple different properties, you will need to join PRODUCT_PROPERTY multiple times each with a unique correlation name.

For properties that not all products have, you might need to use outer joins.

I would strongly suggest you use a better data model than the one you have chosen. There are better ways to model subtypes in a relational dbms.

Fabian Pascal's most recent book _Practical Issues in Database Management: A Reference for the Thinking Practitioner_ has an entire chapter devoted to the topic.

Good luck,
Bob

"Jacob Nordgren" <jacob_nordgren_at_hotmail.com> wrote in message news:47c6b9be.0110130115.3f228624_at_posting.google.com...
> It seems as if you don't understand my question. There is no column
> named "Title" since my database model is very general.
>
> The only column that exists is the PRODUCT_PROPERTY
>
> If I use ORDER BY PRODUCT_PROPERTY the result will be ordered by all
> properties. I want to order by the rows that has
> PRODUCT_PROPERTY.PROPERTYID = 1 ("Title").
>
> / Jacob
>
> "Jim Kennedy" <kennedy-family_at_home.com> wrote in message
news:<W8Dx7.23785$JN.84448_at_news1.sttls1.wa.home.com>...
> > put order by title at the end.
> > Jim
> > "Jacob Nordgren" <jacob_nordgren_at_hotmail.com> wrote in message
> > news:47c6b9be.0110120622.57febc70_at_posting.google.com...
> > > Hi,
> > >
> > > I have the following tables
> > >
> > > PRODUCT_GROUP
> > > ID (INT)* 'Example 1
> > > NAME (VARCHAR) 'Example "Books"
> > >
> > > PRODUCT
> > > ID (INT)* 'Example: 1
> > > PRODUCTGROUPID (INT) 'Example: 1
> > >
> > > PROPERTY
> > > ID (INT)* 'Example 1
> > > NAME (VARCHAR) 'Example "Title"
> > >
> > > PRODUCT_PROPERTY
> > > PRODUCTID (INT)* 'Example 1
> > > PROPERTYID (INT)* 'Example 1
> > > VALUE (VARCHAR)* 'Example "Learn SQL in 24 hours"
> > >
> > > If I want to find books with titles like "SQL" I use the following
query:
> > >
> > > SELECT DISTINCT P.ID AS ID
> > > FROM PRODUCT P, PRODUCT_PROPERTY PP
> > > WHERE P.ID = PP.PRODUCTID
> > > AND P.PRODUCTGROUPID = 1
> > > AND PP.PROPERTYID = 1 AND UPPER(PP.VALUE) LIKE '%SQL%'
> > >
> > > My question is: How can I sort the result by the property "title".
> > >
> > > Thanks for your help,
> > >
> > > Jacob
Received on Sun Oct 14 2001 - 03:59:41 CEST