Retrieve records in the order in which they were added to the table.

A

Anupama

New Member
Hi,

I need to write a query to retrieve records in the order in which they were added to the table. I can't add a timestamp to these records or sequence (since records are added to the table from many programs and I cannot change them) or write a schema trigger for it. Is there any other way to do it?

Thanks for your help.

Anupama.
 
?? problem ??

Greetings,
I do not understand your problem. If it is just the order records were added that is where your ProdNbr (or applicable) field comes in. You just need an integer value to increment on a new row (record added). This is where the use of sequences can come in.
When create a new record (add a new row), give that row / record an integer value. A prodNbr / reference / ID. You can then easily order the records by this integer value and retrieve them in the order created way.
 
There is no secret "order in which records were added" index or magical incantation. Including such schemes as recids -- Progress reuses space so recid order is not a reliable substitute for creation order.

If you want to reliably retrieve data in the order that it was created then you need a field in the table to support that. A sequence would be better than a time-stamp since with a timestamp you could easily have two records with the same time stamp.

I'd add a field and a create trigger. That way you side-step the "lots of programs create these records" problem.

On the other hand... why do you think that you need records in this order? That's a fairly suspicious requirement in the first place.
 
The client has a customised table in which they are adding records which have some meter readings. I need to retrieve the last one added to compare with the values they are entering now. As I said earlier, I can't write a create trigger (which I would essentially require to write on the table itself as a schema trigger).
Do you know of any other way to do it?

Thanks for replying.
Anupama.
 
???

RE:
The client has a customised table
>> DOHH well yes that much is obvious, and a given. There is not a default table format.

RE
can't write a create trigger
>> You are using a database, therefore you do not require a trigger. With efficient database design, there should be a row identifier (a unique refference, such as Nbr, ID and so forth). This identifier is WHAT you require, it should be an integer (though char would work but makes things hard). The row identifier should be sequential also. When a record is created, the record is generated a Nbr, ID... Then when another record is created that is generated a Nbr, ID... sequentially (+1). This is basic db design, not rocket science.
 
mpowell_esq said:
RE:
The client has a customised table
>> DOHH well yes that much is obvious, and a given. There is not a default table format.

RE
can't write a create trigger
>> You are using a database, therefore you do not require a trigger. With efficient database design, there should be a row identifier (a unique refference, such as Nbr, ID and so forth). This identifier is WHAT you require, it should be an integer (though char would work but makes things hard). The row identifier should be sequential also. When a record is created, the record is generated a Nbr, ID... Then when another record is created that is generated a Nbr, ID... sequentially (+1). This is basic db design, not rocket science.
Click to expand...

A unique row identifier so does not make the database more efficient, it creates an overhead when it is written and more of an overhead if it is part of an index. A unique row identifier is only needed to be used as a foreign key or to order the records. The best place to assign a row identifier is in a schema create trigger as it removes the requirement to assign this value in every piece of code that creates a record for the table in question.

Given the restrictions that have been placed of not being able to add fields or a trigger to the table then it is impossible to identify the last record added unless it can be inferred form the other data in the table.
 
thanks

The table has a character field (which is the primary index) which stores a number and a prefix to identify the record (the prefix is to know which program had been used to create the record, so the identifiers are like "ca190", "so120", etc.), a date field to store the creation date, a couple of fields to store the meter reading and numerous unused fields ( a field for storing a unique identifier is not a problem). Sorting by the creation date field hasn't helped much.

Maybe I can convince them that writing a schema create trigger to store a unique identifier would be the best solution.

Thanks for you help.
 
Identifier

RE
the identifiers are like "ca190", "so120",
> So you DO have unique identifiers that CAN be used - no need for rebuild. If the identifier is a consistent format you can (fiddle) that. With your example, are the numbers sequential? IE ca190, ca191, ca192 ..ca197 (fingers crossed). If it is can you see what is required? Where I am going?
 
thanks. I resolved the issue

Thanks to all of u for helping me. I resolved the issue. Actually, the system has another table which keeps track of the issues/shipments done for the orders. I was unaware of the fact that I had to check only for shipped orders. This table has a sequence number which does the job.

Thanks again for helping me out.
 
Deployment

Greetings,

I know the issue has been resolved, but I were looking through the thread again, and made a connection. The table names are familure along with the format use , ca190", "so120". That is similar to that used by QAD in the MFG/PRO product.
 
RE:
A unique row identifier so does not make the database more efficient,
it creates an overhead when it is written and more of an overhead if it is part of an index. A unique row identifier is only needed to be used as a foreign key or to order the records.
>>
Ha ha thud (laughing my head off). Of course a unique row identifier makes a DATABASE more efficient.

RE:
it creates an overhead when it is written and more of an overhead if it is part of an index.

>> That is for a Progress OpenEdge database. I were commenting on making a DATABASE efficient - not applying it. Not all databases operate in the same way as PSC.
I were commenting on database design - which then can be applied in an appropriate environment and method(s).
 
You must log in or register to reply here.
Back
Top