Thanks Adam, Your suggestion has helped me solve the problem half way, but i am yet to get the proper output.
I will repeat the whole process

Master table
CO_CODE Integer
CO_NAME Varchar(16),

Trans Table
CO_CODE Integer NOT NULL,
YRC Integer NOT NULL,
SGTYPE Integer NOT NULL,
SRNO Integer NOT NULL,
STOPRC VARCHAR(8),
STOPRN VARCHAR(35),
STOSEG VARCHAR(35),
STO FLOAT,

Master table primary key - Co_code
Trans table primary key - co_code, yrc, sgtype ,srno

I have attached a sample datasheet
when i run the following query i get the output in proper order as follows

select master.co_code, co_name ,srno,yrc,sgtype from seg_scr,master where seg_scr.co_code = master.co_code order by co_name || '"'


CO_CODE CO_NAME SRNO YRC SGTYPE
2096 3M India 1 200212 1
2096 3M India 2 200212 1
2096 3M India 3 200212 1
2096 3M India 4 200212 1
2096 3M India 5 200212 1
2096 3M India 6 200212 1
2096 3M India 7 200212 1
2096 3M India 9999 200212 1
2096 3M India 1 200312 1
2096 3M India 2 200312 1
2096 3M India 3 200312 1
2096 3M India 4 200312 1
2096 3M India 5 200312 1
2096 3M India 6 200312 1
2096 3M India 9999 200312 1
In the above output the srno field comes in the proper order along with the YRC field


but when i run the following query with the stoseg field the output goes for a toss.
select first 20 master.co_code, co_name ,srno,yrc,sgtype,stoseg from seg_scr,master where seg_scr.co_code = master.co_code order by co_name || '"'

CO_CODE CO_NAME SRNO YRC SGTYPE STOSEG
2096 3M India 4 200212 1 Health Care Markets
2096 3M India 3 200312 1 Health Care Markets
2096 3M India 3 200212 1 Electrical, Telecom & Elec
2096 3M India 1 200212 1 Industrial Markets
2096 3M India 1 200312 1 Industrial Markets
2096 3M India 6 200212 1 Consumer & Office, Constru
2096 3M India 5 200312 1 Consumer & Office, Constru
2096 3M India 9999 200212 1 Total
2096 3M India 9999 200312 1 Total
2096 3M India 5 200212 1 Traffic & Safety Markets
2096 3M India 4 200312 1 Traffic & Safety Markets
2096 3M India 7 200212 1 Others
2096 3M India 6 200312 1 Others
2096 3M India 2 200212 1 Automotive & Speciality Ma
2096 3M India 2 200312 1 Automotive & Speciality Ma
2096 3M India 3 200412 1 Health Care Markets
2096 3M India 1 200412 1 Industrial Markets
2096 3M India 1 200512 1 Industrial Markets
2096 3M India 5 200412 1 Consumer and Office, Constru
2096 3M India 9999 200412 1 Total

In the above output if u notice the srno and the yrc are not in the proper order

As my query is created dynamically from the UI, the field selection order depends on the user and the order by clause remains mandatory on the co_name. I cannot add any other field to the order by clause.

Pls Help.

Thanks
Meena.

----- Original Message -----
From: Adam
To: firebird-support@yahoogroups.com
Sent: Wednesday, October 25, 2006 12:23 PM
Subject: [firebird-support] Re: Indexes in Firebird


--- In firebird-support@yahoogroups.com, "Meena" <iyermeena@...> wrote:
>
> The difference is that when i give the order by clause of a field
from the master table, the output comes ordered by that specific field
and the index of the transaction table is not considered at all.If I
remove the order by clause of the master table field, the index of the
transaction field is effective.

----

It sounds like it is doing an indexed walk through the master table.
You can avoid that by adding 0 to the field, or appending '' if it is
a string.

eg

...
ORDER BY ID+0

...
ORDER BY NAME || ''


Adam







[Non-text portions of this message have been removed]