Recordset Order

[div]

Hi

I wish to pull back a number of records in the form of a recordset.

i.e.
SELECT * FROM directory WHERE id=14723 or id=12212 or id=13221

The problem is the recordset is pulled back in id order
i.e.
12212
13221
14723

rather than the order I need

i.e.
14723
12212
13321

How do I keep control over the order of recordset?

Thanks

Div

[gwinters]

You can use an ORDER BY clause at the end of the statement to order by whatever criteria you need to use.

Ex-

Select * from person where id = 1300 or id = 1350
order by last_name, first_name


Otherwise, you will have to put the data into a structure on the code side and write a function to order it.

[vass0922]

to follow on gwinters post, if you want the last/first names in a particular order you can say

ORDER BY last_name ASC, firstname DESC .. for ascending/descending order

(hope I didn't bork up that sql, I haven't used asc and desc for awhile lol)

[gwinters]

Looks perfect to me and I just used it five minutes ago. I'm at work right now, of course.

[div]

Sorry I didn't make my self clear.

The list isn't in any numerical order - so the order by clause won't work ( i assume).

I need to pull the records by id -

the ids in Select clause can be random (although valid) and must be kept in the order they are generated.

Thanks for the replies so far.

[howste]

I don't think you can use ORDER BY unless you have something to put in order. Do you have some kind of generation date or time field you can sort by? If not, can you create another field in the table that you can use to sort the data? You don't have to display the field, just use it to sort.

[Whir]

Is this some kind of form interaction? Could you parse out the query data (selected numbers or text input for the ID) and run a new query for each? I realize that's more overhead, but if you haven't got a field to sort by, and you need them in the order selected...

[ctaylor]

My experience is that queries are automatically ordered in ascending order according to the first field retrieved

is there a primary key for the database? any autonumber fields? how about a now() field in each record in order to capture the time and date the record was entered into the database?

try selecting one of those as the first field of your query if possible

If you cannot sort by any of these fields, you might want to consider adding a field to the underlying table to record now() whenever a record is entered.

this will allow you to sort by the now() field to preserve the data entry order for your needs.

[gwinters]

Try this:

select * from table1 where id = 1300
union all
select * from table1 where id = 1250
union all
select * from table1 where id = 1400



That would be the only way that I can think of. If you had a list of dynamic length, then you would have to use sql strings and make your query as you go along. Or you could make a temp table and insert your results into it in the order that you wanted them. That may not work if it wants to order them by ID (you'd end up right where you started), but then you could create a column that uses a timestamp and order by it as suggested above.

[^hyd^]

Quote:

I need to pull the records by id

...you can't use ...ORDER BY id ASC/DESC ???