Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> 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 Sat Oct 13 2001 - 20:59:41 CDT