Custom Order By

Hi,

I want to ask something. Is there any possibility to use order by using our own criteria. Example: I want to order my query by ProductName with specified criteria. See below

Products:

ProductID ProductName

1 Book

2 Pen

3 Ruler

The query for "select * from Products order by ProductName" will actually result

1 Book

2 Pen

3 Ruler

I want the result be like this:

2 Pen

1 Book

3 Ruler

May be my crazy idea is using this kind of query:

"select * from Products order by ProductName ( 'Pen' , 'Book' , 'Ruler' )"

Thanks in advance .....

If you have a limited number of 'sort by' items, you could use a CASE structure.

SELECT
Col1,
Col2,
Col3,
etc
FROM MyTable
ORDER BY CASE Col2
WHEN 'Pen' THEN 1
WHEN 'Book' THEN 2
WHEN 'Ruler' THEN 3
END

|||

it works, great !!! ...

thanks ....