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

From: Marc Blum <marc_at_marcblum.de>
Date: Sun, 14 Oct 2001 18:55:10 GMT
Message-ID: <3bc9ddaf.2602341@news.online.de>

On 12 Oct 2001 07:22:33 -0700, jacob_nordgren_at_hotmail.com (Jacob Nordgren) wrote:

>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

I'm nor sure why you are using distinct, I would think that each "book" has only one property "title", therefore I comment out the DISTINCT: SELECT /* DISTINCT */

                  P.ID AS ID,
                  PP.VALUE as TITLE
    FROM PRODUCT                             P, 
                  PRODUCT_PROPERTY PP
  WHERE P.ID = PP.PRODUCTID
          AND P.PRODUCTGROUPID = 1  --means Books
          AND PP.PROPERTYID = 1 -- means Title
          AND UPPER(PP.VALUE) LIKE '%SQL%'

 ORDER BY 2; Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Sun Oct 14 2001 - 13:55:10 CDT