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

1. Please post DDL and sample data, so people don't hav to guess aobut constriatns, datatypes, keys, etc.
2. Please read a book on relational design or take a course. This thing is not even in First Normal Form. You are trying to meta-data into an SQL database for an OO design. IT DOES NOT WORK! I know; I get paid to fix these schemas.
3. Stop using meanignless names like "id" - identifier of what?? Next, you will be using system generated id numbers and huge VARCHAR(n) columns, which will destroy your schema. "Property" is not an attribute, so it cannot be a column in a table. "value" is both vague (value of what?) and meaningless -- a value is what all the columns hold.

Here is a relational design:

CREATE TABLE ProductGroups
(group_id INTEGER NOT NULL PRIMARY KEY,
 group_name VARCHAR (30) NOT NULL);

CREATE TABLE Products
(upc DECIMAL(10,0) NOT NULL PRIMARY KEY, -- or use EAN  group_id INTEGER NOT NULL

          REFERENCES ProductGroups(group_id),  title VARCHAR (30) NOT NULL DEFAULT '{{no title}})'  ... );

>> 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%'; <<

Compare this to:

SELECT upc
  FROM Products AS P1
 WHERE title LIKE '%SQL%'
 ORDER BY title;

Every attribute will require 2 or 3 tables, every query will grow and grow, almost exponentially and be impossible to maintain. The response time will slow down. But the schema has no constraints or checking, so a single data error destroys the entire schema. What if you have two rows with 'title' wth different id numbers? Received on Sat Oct 13 2001 - 10:21:43 CDT