RE: SQL and Order By Question

From: Michael_Schutte@xxxxxxxxxxxx

You really shouldn't do this, or the case statement as suggested by
someone
else. Cuz now you're locked into hardcoding. If you cannot use another
field, then you should look into the custom sort as suggested by others.

That's a great point, Michael!

What you SHOULD do, Ashish, is to set up a second sort file that has two
fields: the original value in the data file and the order you want it:

RANKFILE:
VALUE RANK
A 1
B 4
C 2
D 3

select table1.*, rank from table1 join RANKFILE
on table1.field = rankfile.value
order by RANK

This will return a result set with all of the fields in table1 and then the
rank value. It will only include records in table1 which have a matching
entry in rankfile and it will include them in the rank sequence.

The beauty of this approach is that you can put RANKFILE in QTEMP and then
write values to it on the fly to change your selection and sort criteria.

If, though, all you want is selection of a group of values, you can do this:

select * from table where field1 in ('a', 'b', 'c', 'd')

That SHOULD get you the records in physical sequence in the file, although
that's not guaranteed and to be absolutely certain you should probably
include an order by clause:

select * from table1 where field in ('a', 'b', 'c', 'd')
order by rrn(table1)

Joe