Order By

  • Thread starter Thread starter JonWayn
  • Start date Start date
J

JonWayn

When used in an append query, does the 'Order By' clause affect the order in
which records are appended?
 
JonWayn said:
When used in an append query, does the 'Order By' clause affect the order in
which records are appended?
Click to expand...


An Insert Into query doesn't have an Order By clause, but it
wouldn't matter if it did.

Records in a realational database
table have no inherent order.

If you want to see the records in a particular order, you
have to use a query with an Order By clause to display the
records.
 
Actually, I believe Access does honor the Order By criteria in an insert
query.

This is useful if you have multiple source records with the same key field,
and you want to give a preference to some records over a other records,
based on a non-key field sort order.

- Steve
 
Interesting, Sky. I suppose that could be useful along with
a TOP predicate. Do you have an example of another way to
take advantage of this capability?

I don't think this has anything to do with "affect the order
in which records are appended", or am I missing the point?
 
SQL table really does not have inherent order. If the table is selected in
another way, perhaps joined to some other table, the sequence will be
different (but then maybe not). The point is you cannot count on it.



Stephen K. Young said:
Actually, I believe Access does honor the Order By criteria in an insert
query.

This is useful if you have multiple source records with the same key field,
and you want to give a preference to some records over a other records,
based on a non-key field sort order.

- Steve

order
Click to expand...
 
I understand what you described. Did some of that for conversion. However,
once inserted, the records cannot be assumed to be in any particular order.

Stephen K. Young said:
I once had occasion to use an Order By criteria in an Insert query to import
thousands of records from an external source. The external data was mostly
ok, but it did not have an enforced key field, so naturally there were
duplicate records, some with missing data or bad data.

To import this, I could have made a Group By query on the key field and
inserted the max of the data, or skipped nulls, or performed various other
manipulations. But I wanted to get complete records, not a mix of values
from different duplicate records, and it would have taken several queries in
sequence to select the proper records. Instead, I simply sorted by the
preference criteria for acceptance, using a single Insert query into a keyed
table. This way, the query inserted individual records without mixing data
between records, but the preferred records were inserted first, and
remaining records were rejected as key violations. The important thing is
that the preferred records were inserted first in order to reject the
remaining records.

If desired, I could then have a separate query to display the duplicate
records.

I would consider this an implementation trick that works with Jet. I don't
know what the ANSI SQL standard says about Order By criteria on an Insert
query.

A Top query would not work for this, because I needed to perform sorting and
selecting grouped for each key field value, whereas a Top query orders and
subsets the entire table at once.

- Steve

Marshall Barton said:
Interesting, Sky. I suppose that could be useful along with
a TOP predicate. Do you have an example of another way to
take advantage of this capability?

I don't think this has anything to do with "affect the order
in which records are appended", or am I missing the point?
--
Marsh
MVP [MS Access]



Stephen K. Young said:
Actually, I believe Access does honor the Order By criteria in an insert
query.

This is useful if you have multiple source records with the same key field,
and you want to give a preference to some records over a other records,
based on a non-key field sort order.

- Steve

JonWayn wrote:
When used in an append query, does the 'Order By' clause
affect the order in which records are appended?
Click to expand...
Click to expand...
Click to expand...
 
I once had occasion to use an Order By criteria in an Insert query to import
thousands of records from an external source. The external data was mostly
ok, but it did not have an enforced key field, so naturally there were
duplicate records, some with missing data or bad data.

To import this, I could have made a Group By query on the key field and
inserted the max of the data, or skipped nulls, or performed various other
manipulations. But I wanted to get complete records, not a mix of values
from different duplicate records, and it would have taken several queries in
sequence to select the proper records. Instead, I simply sorted by the
preference criteria for acceptance, using a single Insert query into a keyed
table. This way, the query inserted individual records without mixing data
between records, but the preferred records were inserted first, and
remaining records were rejected as key violations. The important thing is
that the preferred records were inserted first in order to reject the
remaining records.

If desired, I could then have a separate query to display the duplicate
records.

I would consider this an implementation trick that works with Jet. I don't
know what the ANSI SQL standard says about Order By criteria on an Insert
query.

A Top query would not work for this, because I needed to perform sorting and
selecting grouped for each key field value, whereas a Top query orders and
subsets the entire table at once.

- Steve

Marshall Barton said:
Interesting, Sky. I suppose that could be useful along with
a TOP predicate. Do you have an example of another way to
take advantage of this capability?

I don't think this has anything to do with "affect the order
in which records are appended", or am I missing the point?
--
Marsh
MVP [MS Access]



Stephen K. Young said:
Actually, I believe Access does honor the Order By criteria in an insert
query.

This is useful if you have multiple source records with the same key field,
and you want to give a preference to some records over a other records,
based on a non-key field sort order.

- Steve
Click to expand...
Click to expand...
 
Thank you. That is exactly how I hoped it would behave. My scenario is
similar to yours, except it is all done locally from within a single
database. I am appending records from a table in which all records has
duplicates in a few important fields and, from each group, I want to keep
only the record with the highest priority in a particular field and dump the
rest. I treated it just like you said, ie. append them to a table which had
primary key restrictions.

Thank you once again


Stephen K. Young said:
I once had occasion to use an Order By criteria in an Insert query to import
thousands of records from an external source. The external data was mostly
ok, but it did not have an enforced key field, so naturally there were
duplicate records, some with missing data or bad data.

To import this, I could have made a Group By query on the key field and
inserted the max of the data, or skipped nulls, or performed various other
manipulations. But I wanted to get complete records, not a mix of values
from different duplicate records, and it would have taken several queries in
sequence to select the proper records. Instead, I simply sorted by the
preference criteria for acceptance, using a single Insert query into a keyed
table. This way, the query inserted individual records without mixing data
between records, but the preferred records were inserted first, and
remaining records were rejected as key violations. The important thing is
that the preferred records were inserted first in order to reject the
remaining records.

If desired, I could then have a separate query to display the duplicate
records.

I would consider this an implementation trick that works with Jet. I don't
know what the ANSI SQL standard says about Order By criteria on an Insert
query.

A Top query would not work for this, because I needed to perform sorting and
selecting grouped for each key field value, whereas a Top query orders and
subsets the entire table at once.

- Steve

Marshall Barton said:
Interesting, Sky. I suppose that could be useful along with
a TOP predicate. Do you have an example of another way to
take advantage of this capability?

I don't think this has anything to do with "affect the order
in which records are appended", or am I missing the point?
--
Marsh
MVP [MS Access]



Stephen K. Young said:
Actually, I believe Access does honor the Order By criteria in an insert
query.

This is useful if you have multiple source records with the same key field,
and you want to give a preference to some records over a other records,
based on a non-key field sort order.

- Steve

JonWayn wrote:
When used in an append query, does the 'Order By' clause
affect the order in which records are appended?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top