Hi Meena!

If you specify

ORDER BY CO_NAME

and gets the output ordered by

CO_NAME, YRC, SRNO

then that is purely accidental. You've specified that you want CO_NAME
ordered, but that also implicitly means that you don't care about the
order for the rest of the fields and that Firebird is free to return
them in random order. I guess the order they're atcually returned
depends on the SELECT clause, the order they were inserted into the
table and it could also (in theory) change between versions of Firebird
or just randomly.

There is no alternative to specify all those fields that you want to
order by in the ORDER BY clause.

Though

ORDER BY CO_NAME, YRC, SRNO

is (in your case) equivalent to

ORDER BY 2, 4, 3

I think (though I've not tried) you can also do

ORDER BY CO_NAME, 4, 3

Sorry that Firebird doesn't work like you think it does. Neither of your
SELECT clauses guarantee you any order beyond the CO_NAME, you were just
unlucky that it accidentally returned YRC, SRNO like you wanted it to
(unlucky because at one point someone doing something similar will not
get the same order).

Set

Meena wrote:

> 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.