Subject Re: ORDER OF TABLE
From Mervyn Bick <invalid@invalid.invald>
Date Fri, 22 Mar 2019 10:52:43 +0200
Newsgroups dbase.getting-started

On 2019-03-21 8:55 PM, Mustansir Ghor wrote:
> Dear All
>
> I have an SQL statement with single table as follows. Its browse is attached as Capture2.
> select * from pharmrec
>
> I have same table opened with a join statement in order to relate item name as follows. Its browse is attached as Capture3
> select * from pharmrec a join pharm p on a.item=p.code
>
> My problem is I need to use join table in order to generate my report. But order of records in a single table that were entered in the grid are changed in the join table although order by clause were not used.
>
> So how to use the join table and yet report to generate same order of records as were entered in the grid. Can anybody help me on this. In dbaseIV we called it natural order.


When the JOIN is made the localSQL engine has internally ordered the
pharmrec table on its code field and the pharm table on its code field.
This is to allow the localSQL engine to make the join most efficiently.
This results in the resulting JOINed rowset being in code order.

If you want to present the records in the order natural order of records
  pharmrec you are going to need to provide a field in pharmrec that can
be used in an ORDER BY clause after the join has been made.

Execute the following command in the Command Panel to add the field seq
This requires exclusive use of the table so it will fail if the table is
in use elsewhere.

  alter table pharmrec add seq int

If you place the following code in the header section of your report it
will ensure that the seq field is up-to-date whenever the report is run.

*****
use pharmrec
replace all seq with recno()
use
******

This does not require exclusive use of the table so it will work even if
the table is in use elsewhere.

Now you can use the following in your report

select * from pharmrec a join pharm p on a.item=p.code order by seq


and this should give you the order you want.

Mervyn.