Select without an order by clause doesn't return the same order as the database

[MelanieParent]

Hello,

I have a select command that gets all the products from an order and I want to list them in the order they were entered in the database. There is no ORDER BY clause. I'm wondering why it doesn't list them that way. When I print the order, it's correct, however when I print the invoice the order is messed up. Anyone knows why that could be ?

Thanks for your help, have a great day !

Melanie

 

[Mike Lewis]

Hello Melanie.

First, let's be clear about one thing. When you refer to a "database", do you mean a FoxPro table, that is, a DBF file - as opposed to a database on a back-end server, such as SQL Server? It's an important distinction.

Assuming you mean a FoxPro DBF, then it's safe to assume that the physical order of the records in the table will match the order in which they were originally entered (allowing for records that have since been deleted). To preserve that order in your results, you should add this clause to your SELECT:

[tt]SELECT ... FROM ... [highlight #FCE94F]ORDER BY RECNO()[/highlight][/tt]

That won't work with a back-end server because these generally do not have the concept of a record number.

I hope this helps.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[MelanieParent]

You were correct, I was talking about a Foxpro DBF.

When I try to order it by recno() it tells me that Queries of this type are not allowed. (It's a multiple table query). I tried to include the product database recno number into my query but it wouldn't work either (product.recno()as rec).

The workaround I did so far is that I put a position value into my query at 0 and did a Do While !eof() and when it was the lines I wanted to be last (the taxes) I changed the position value to 1 and before printing I order the query on position. It is not pretty but so far it works.

I'll continue looking into it as I've been using this program forever with other companies (with all the same dbfs structures) and only the last company that was created that is giving me this problem.

Thanks for your help.

 

[Mike Lewis]

Actually, the ideal solution would be to add a field to the table that records the order in which the records were added. Then you could simply use that field in your ORDER BY clause.

The field could be a straight sequence number, which can be generated automatically by making the field an Integer Autoinc data type. Or, perahps more usefully, it could be a datetime. In that case, your program would have to update that field with the current datetime at the point at which the record is added.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Actually, the ideal solution would be to add a field to the table that records the order in which the records were added.

Of course, that won't help you deal with an existing table. You wouldn't be able to add the field retrospectively. But what you could do is to add an integer field, initially set to zero. Then you could populate the field with a sequence number by doing [tt]REPLACE ALL WITH RECNO()[/tt].

You could do that immediately before you run the query. Or you could do it once only, when you first create the new field. And then change the field to an Integer Autoinc (use [tt]ALTER TABLE ... ALTER COLUMNM ... INTEGER AUTOINC NEXTVALUE x [/tt], where x is one greater than the record number of the last (non-deleted) record in the table.

Some other possibilities come to mind, but give this idea some thought first.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

I feel I would need to have an M first name to qualify for this thread.

If you really just mean the physical record order of a DBF, one simple way to get the range of records about an order/invoice is to use COPY TO instead of SQL, or even just SCAN/ENDSCAN while the dbf is in no specific index order.

Which gets me to the question that still is open with the answer assumed by all of us, but what is seen in a browse, really, when no order is set? As you see a difference in the report of the order vs the report of the invoice, there will be some difference in them. If relations are used, indexes are involved for the master/slave 1:1 or 1:n relation to work, and with that, you can also get a sort order that's not the physical order.

The relation may not be set programmatically, but within the visual data environment of the reports.

Chriss

 

[TamarGranor]

You can do what you want like this:

[pre]
SELECT RECNO() as nRecNo, field1, field2, ... ;
FROM YourTable ;
ORDER BY 1 ;
INTO CURSOR Whatever
[/pre]

If you'd rather not have the record # in the final cursor, use a subquery:

[pre]
SELECT field1, field2, ... ;
FROM (SELECT RECNO() as nRecNo, field1, field2, ... ;
FROM YourTable) YourTableWithRecno ;
ORDER BY nRecNo ;
INTO CURSOR Whatever
[/pre]

Whichever way you do this, be aware that it only works if there's just one table in the query. You can't use any function that takes an alias parameter in a query with more than one table.

Tamar

 

[Mike Lewis]

You can't use any function that takes an alias parameter in a query with more than one table.

That's true. But what she can do is this:
[tt]
SELECT RECNO() as nRecNo, field1, field2, ... ;
FROM YourTable ;
ORDER BY 1 ;
INTO CURSOR Whatever[/tt]

and then use the cursor Whatever in the original query (with [tt]ORDER BY nRecNo[/tt]) in place of the main table (YourTable in this example).

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[MelanieParent]

Thank you all for all your tips. I ended up doing Mike's last suggestion and it works great!

Thanks to all of you for taking some time to help me. I really appreciate it !

Melanie

 

[Mike Lewis]

Glad you got it working, Melanie. And thanks for letting us know.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads