I have multiple lists of attributes which represent the downstream sequence of river segments from headwater to marine outlet.

SELECT * FROM [WTRCRS Table] WHERE [STREAM] IN (83805,83647,83515,83284,83283,83282)

Using these, I want to be able to develop queries to select a subset from a larger table, but I also need to guarantee that the selected records are returned in the order specified by the stream list for further processing. This appears not to be the case.

Does anyone know how to enforce a particular order in returned results? Unfortunately, there is nothing in the table to enforce this order or other combinations.

I had wondered if I could use something like a VALUES() statement and the sequence CROSS JOIN(ed) with a ValueSequence to create an index for use with ordering the result but have so far failed to make anything that works.

The result might look something like ...

83805,1

83647,2

83515,3

83284,4

83283,5

83282,6

Any thoughts much appreciated, and some test data attached.

Attachments:
M9 FORUM.mxb

Landsystems Ltd ... Know your land | www.landsystems.co.nz

SELECT *

FROM [WTRCRS Table] AS [BASE]

INNER JOIN

(VALUES (83805),(83647),(83515),(83284),(83283),(83282) AS ([STRM]))

ON [BASE].[STREAM] = [STRM];

This appears to work if I rejig my stream ID string, but I am keen to hear if there are any options using ValueSequence as I often need to generate something like the result above within a query

Landsystems Ltd ... Know your land | www.landsystems.co.nz

That approach still does not guarantee a particular order Dan.

You could finangle something with a CASE expression with an ORDER BY, but...

IMHO, this is not a question well answered using SQL. You are better off with a procedural language here.

Hi Tim, thanks for responding.

I am actually using procedural code to launch and process on the result of the SQL, which is embedded within my procedural script.

I will have a think about how to tackle this. Many thanks

Landsystems Ltd ... Know your land | www.landsystems.co.nz