--- In firebird-support@yahoogroups.com, "Meena" <iyermeena@...> 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


No, they are correctly ordered by co_name. Your mistake is to expect
anything about the order of two records where they have the same co_name.

Unless you include something about it in the order by clause, two
records that are equal in sense of what you have in the order by
clause, then they will be returned in an unpredictable order that may
even change from one day to the next.

Maybe you want something more like:

order by co_name || '', SRNO

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

Bad luck. You need to review the SQL Standard. Firebird will not waste
time sorting two records that are equal under the order by clause,
what a waste of time. If you want the fields to be in a given order,
make sure that field is in the order by.

Adam