Order of records in a select

[volfreak]

All,

This may be elementary but I'm somehow 'missing it'.

I have a table, say CLASSES, with the following records (and this is the order entered into the table - record number order - no order set):

PKID TITLE
0001 Class 4
0002 Class 2
0003 Class 3

If I order ascending on TITLE, the PKIDs are sorted as:
0002
0003
0001

I want to select *just* the PKIDs AND keep them in the order they are in when the table is ordered by title.

However, every select I do always reorders them in the order they were entered into the table (record number order).

Is there a way I can use a SELECT statement AND preserve the order that is used in the source table?

Or must I loop through the source table and INSERT the records into the temporary table to keep the order?

Thanks much in advance for any help.

 

[ramani]

Hi Volfrek...

The sort should sort all the fields accrding to the sort order. In other words.. the sort should sort by records.. the whole of rows.. and so you cannot keep in one row the information about other rows..

However if your intention is to sort by field no1.. then by field no2..

Then in the sort order you can put the code..
ORDER BY firld2, field1
or
ORDER BY field1,field2

ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[volfreak]

Thanks Ramani,

I understand what you're saying. But my select statement is only pulling field1 and I want to keep the order that is set in the source table. However, without using any ORDER clause, the field1 records are SELECTed into the cursor in RECNO() order and not in the order that is SET on the source table.

Adding the ORDER BY just puts it in field1 order.

I got it fixed by getting the ('ORDER') of the source table, determining whether it's ASC or DESC, putting that to a variable and using that variable as the ORDER BY clause.

Thanks again for the quick reply.

 

[rgbean]

In an SQL SELECT statement, you can only ORDER BY a field that is selected. That's because the ordering is done AFTER the fields are selected - if you don't select it, you can't order by it!

Rick

 

[volfreak]

Actually that's not correct!

Here's a way to give it a try.

Create a program file and paste the following in. Then run it.

------- BEGIN PROGRAM CODE
local m.pkid, m.mytitle, ls_Order, ls_SortOrder

create table foo (pkid C(4), mytitle C(50))
sele foo
m.pkid = "0002"
m.mytitle = "Sunshine"
insert into foo from memvar

m.pkid = "0003"
m.mytitle = "Full"
insert into foo from memvar

m.pkid = "0001"
m.mytitle = "Zippy"
insert into foo from memvar

m.pkid = "0004"
m.mytitle = "Tangerine"
insert into foo from memvar

sele foo
index on mytitle tag mytitle

ls_order = set('order')
ls_Sortorder = substr(ls_order,5,atc(" ",ls_order,2)-5) + iif("DESCEND" $ ls_order," DESC"," ASC&quot

sele pkid from foo into cursor tempz2 order by &ls_SortOrder

sele tempz2

brow last

RETURN && End Program

The records are displayed in the tempz2 cursor as:

0003
0002
0004
0001

The order they were entered is 2, 3, 1 and 4.

Notice, I only selected the pkid but it kept the records in the order as they were in the 'source' table.

So, you can order by something other than the field(s) that is/are being selected.

Cheers,