I would point out that, whenever you are repeating a text value in a column of
a table, you should probably be using a surrogate key to a lookup table
instead. Your new requirement for a specific sort order for the codes drives
this point home. If you create a lookup table for your codes, you can add a
sort order attribute to the codes table and not be forced to choose between
adding another column to your main table or using SQL that will be invalid the
next time a code is added.
Example:
paper_type
----------
(pk) paper_type_id int identity
(u) paper_type_code varchar(10)
order_sequence int
document
--------
(pk) document_id
(fk) paper_type_id
document_title
To query from these tables in the document type sort order, ...
SELECT
document.document_title,
paper_type.paper_type_code
FROM
document
INNER JOIN paper_type
ON document.paper_type_id = paper_type.paper_type_id
ORDER BY
paper_type.order_sequence
document.document_title
On 9 Dec 2004 07:53:16 -0800, "Alpine7" <da****@datashock.com> wrote:
How can I order the results of my query in non-linear fasion. I have a
field with these values: Reg S, 144A, US and want to order my results
by US, 144A, Reg S.
I would prefer not to create another field in the table if possible.