Union sort

[rachael]

I have a union query that is combining the results of several other queries.
I have a specific sort order that I need to maintain – this sort order is
built into the individual queries, but not the union query that joins them
together. The problem is there is no easy way to use ORDER BY on the union
results to get the order I need. What I need at the end is an order like:

Organization A – [blank field] – [blank field] – Geography A
Organization A – [blank field] – [blank field] – Geography B
Organization A – [blank field] – [blank field] – Geography C
--------------------------------------------------------------------- [end
of 1st query]
Organization O – [blank field] – [blank field] – [blank field]
Organization O – [SubOrg A] – [blank field] – [blank field]
Organization O – [SubOrg A] – [Group A] – [blank field]
Organization O – [SubOrg A] – [Group B] – [blank field]
Organization O – [SubOrg A] – [Group C] – [blank field]
Organization O – [SubOrg B] – [blank field] – [blank field]
Organization O – [SubOrg C] – [blank field] – [blank field]
--------------------------------------------------------------------- [end
of 2nd query]
Organization M – [blank field] – [blank field] – [blank field]
Organization M – [SubOrg A] – [blank field] – [blank field]
Organization M – [SubOrg B] – [blank field] – [blank field]
Organization M – [SubOrg C] – [blank field] – [blank field]
--------------------------------------------------------------------- [end
of 3rd query]
Organization C – [blank field] – [blank field] – [blank field]
Organization D – [blank field] – [blank field] – [blank field]
Organization E – [blank field] – [blank field] – [blank field]
--------------------------------------------------------------------- [end
of 4th query]
etc...

I can’t really create a static table (or don’t know how) that lists the
proper sort order for these results, which I could then link to the union
results to sort after the fact. The organizations, etc. in this data will
change over time and it would be difficult to maintain a static table. Any
advice?

Thanks
rachael

 

[geebee]

Hi,

Create a SELECT query, based on your stored UNION QUERY. With that query,
you can select all the columns from your UNION QUERY and sort accordingly.
This would eliminate the ned to create any kind of table. Each time you run
the SELECT QUERY with the UNION QUERY as the source for columns, the UNION
QUERY would in turn/therfore be updated.

geebee

I have a union query that is combining the results of several other queries.
I have a specific sort order that I need to maintain – this sort order is
built into the individual queries, but not the union query that joins them
together. The problem is there is no easy way to use ORDER BY on the union
results to get the order I need. What I need at the end is an order like:

Organization A – [blank field] – [blank field] – Geography A
Organization A – [blank field] – [blank field] – Geography B
Organization A – [blank field] – [blank field] – Geography C
--------------------------------------------------------------------- [end
of 1st query]
Organization O – [blank field] – [blank field] – [blank field]
Organization O – [SubOrg A] – [blank field] – [blank field]
Organization O – [SubOrg A] – [Group A] – [blank field]
Organization O – [SubOrg A] – [Group B] – [blank field]
Organization O – [SubOrg A] – [Group C] – [blank field]
Organization O – [SubOrg B] – [blank field] – [blank field]
Organization O – [SubOrg C] – [blank field] – [blank field]
--------------------------------------------------------------------- [end
of 2nd query]
Organization M – [blank field] – [blank field] – [blank field]
Organization M – [SubOrg A] – [blank field] – [blank field]
Organization M – [SubOrg B] – [blank field] – [blank field]
Organization M – [SubOrg C] – [blank field] – [blank field]
--------------------------------------------------------------------- [end
of 3rd query]
Organization C – [blank field] – [blank field] – [blank field]
Organization D – [blank field] – [blank field] – [blank field]
Organization E – [blank field] – [blank field] – [blank field]
--------------------------------------------------------------------- [end
of 4th query]
etc...

I can’t really create a static table (or don’t know how) that lists the
proper sort order for these results, which I could then link to the union
results to sort after the fact. The organizations, etc. in this data will
change over time and it would be difficult to maintain a static table. Any
advice?

Thanks
rachael

 

[rachael]

Thanks, geebee, but i don't see how that will help. The data are sorted
within each query used in the union, but the overall results aren't sorted in
a simple way. For instance, you'll see below that the first group of Org
results starts with A, the second starts with O, the third starts with M,
etc. That's the order i need to maintain.

Unless i'm missing something with your suggestion, which is always possible.


rachael

Hi,

Create a SELECT query, based on your stored UNION QUERY. With that query,
you can select all the columns from your UNION QUERY and sort accordingly.
This would eliminate the ned to create any kind of table. Each time you run
the SELECT QUERY with the UNION QUERY as the source for columns, the UNION
QUERY would in turn/therfore be updated.

geebee

I have a union query that is combining the results of several other queries.
I have a specific sort order that I need to maintain – this sort order is
built into the individual queries, but not the union query that joins them
together. The problem is there is no easy way to use ORDER BY on the union
results to get the order I need. What I need at the end is an order like:

Organization A – [blank field] – [blank field] – Geography A
Organization A – [blank field] – [blank field] – Geography B
Organization A – [blank field] – [blank field] – Geography C
--------------------------------------------------------------------- [end
of 1st query]
Organization O – [blank field] – [blank field] – [blank field]
Organization O – [SubOrg A] – [blank field] – [blank field]
Organization O – [SubOrg A] – [Group A] – [blank field]
Organization O – [SubOrg A] – [Group B] – [blank field]
Organization O – [SubOrg A] – [Group C] – [blank field]
Organization O – [SubOrg B] – [blank field] – [blank field]
Organization O – [SubOrg C] – [blank field] – [blank field]
--------------------------------------------------------------------- [end
of 2nd query]
Organization M – [blank field] – [blank field] – [blank field]
Organization M – [SubOrg A] – [blank field] – [blank field]
Organization M – [SubOrg B] – [blank field] – [blank field]
Organization M – [SubOrg C] – [blank field] – [blank field]
--------------------------------------------------------------------- [end
of 3rd query]
Organization C – [blank field] – [blank field] – [blank field]
Organization D – [blank field] – [blank field] – [blank field]
Organization E – [blank field] – [blank field] – [blank field]
--------------------------------------------------------------------- [end
of 4th query]
etc...

I can’t really create a static table (or don’t know how) that lists the
proper sort order for these results, which I could then link to the union
results to sort after the fact. The organizations, etc. in this data will
change over time and it would be difficult to maintain a static table. Any
advice?

Thanks
rachael

 

[David F Cox]

You can treat the UNION query as though it were a table, and SELECT and
ORDER BY
You will have a bit of work with field names, alas.

FWIW this worked:

SELECT pets AS [pets_], name1 as [name1_]
FROM
(SELECT Pets, name1 FROM Table1)
UNION
(SELECT location AS Pets, Name1 FROM Table1)
ORDER BY [Pets_];

 

[geebee]

hi,

The UNION QUERY loses the desired sort order maintained by its individual
comprising queries, but your new SELECT QUERY with the UNION QUERY being the
"query source" should re-sort your results as desired. If you post the UNION
QUERY, I can suggest a new query.

geebee

Thanks, geebee, but i don't see how that will help. The data are sorted
within each query used in the union, but the overall results aren't sorted in
a simple way. For instance, you'll see below that the first group of Org
results starts with A, the second starts with O, the third starts with M,
etc. That's the order i need to maintain.

Unless i'm missing something with your suggestion, which is always possible.


rachael

Hi,

Create a SELECT query, based on your stored UNION QUERY. With that query,
you can select all the columns from your UNION QUERY and sort accordingly.
This would eliminate the ned to create any kind of table. Each time you run
the SELECT QUERY with the UNION QUERY as the source for columns, the UNION
QUERY would in turn/therfore be updated.

geebee

I have a union query that is combining the results of several other queries.
I have a specific sort order that I need to maintain – this sort order is
built into the individual queries, but not the union query that joins them
together. The problem is there is no easy way to use ORDER BY on the union
results to get the order I need. What I need at the end is an order like:

Organization A – [blank field] – [blank field] – Geography A
Organization A – [blank field] – [blank field] – Geography B
Organization A – [blank field] – [blank field] – Geography C
--------------------------------------------------------------------- [end
of 1st query]
Organization O – [blank field] – [blank field] – [blank field]
Organization O – [SubOrg A] – [blank field] – [blank field]
Organization O – [SubOrg A] – [Group A] – [blank field]
Organization O – [SubOrg A] – [Group B] – [blank field]
Organization O – [SubOrg A] – [Group C] – [blank field]
Organization O – [SubOrg B] – [blank field] – [blank field]
Organization O – [SubOrg C] – [blank field] – [blank field]
--------------------------------------------------------------------- [end
of 2nd query]
Organization M – [blank field] – [blank field] – [blank field]
Organization M – [SubOrg A] – [blank field] – [blank field]
Organization M – [SubOrg B] – [blank field] – [blank field]
Organization M – [SubOrg C] – [blank field] – [blank field]
--------------------------------------------------------------------- [end
of 3rd query]
Organization C – [blank field] – [blank field] – [blank field]
Organization D – [blank field] – [blank field] – [blank field]
Organization E – [blank field] – [blank field] – [blank field]
--------------------------------------------------------------------- [end
of 4th query]
etc...

I can’t really create a static table (or don’t know how) that lists the
proper sort order for these results, which I could then link to the union
results to sort after the fact. The organizations, etc. in this data will
change over time and it would be difficult to maintain a static table. Any
advice?

Thanks
rachael

 

[David F Cox]

As well suggested by geebee (thanks) I saved my union query and used it as
an input to another query. This worked.

SELECT unionpets.Pets, unionpets.name1
FROM unionpets
ORDER BY unionpets.Pets, unionpets.name1 DESC;

I would have approached this problem the same way, but was intent on
improving my SQL skills, and also demonstrating that a SELECT query can be
used in place of a table. Geebee's way is far superior.

hi,

The UNION QUERY loses the desired sort order maintained by its individual
comprising queries, but your new SELECT QUERY with the UNION QUERY being
the
"query source" should re-sort your results as desired. If you post the
UNION
QUERY, I can suggest a new query.

geebee

Thanks, geebee, but i don't see how that will help. The data are sorted
within each query used in the union, but the overall results aren't
sorted in
a simple way. For instance, you'll see below that the first group of Org
results starts with A, the second starts with O, the third starts with M,
etc. That's the order i need to maintain.

Unless i'm missing something with your suggestion, which is always
possible.


rachael

Hi,

Create a SELECT query, based on your stored UNION QUERY. With that
query,
you can select all the columns from your UNION QUERY and sort
accordingly.
This would eliminate the ned to create any kind of table. Each time
you run
the SELECT QUERY with the UNION QUERY as the source for columns, the
UNION
QUERY would in turn/therfore be updated.

geebee


:

I have a union query that is combining the results of several other
queries.
I have a specific sort order that I need to maintain - this sort
order is
built into the individual queries, but not the union query that joins
them
together. The problem is there is no easy way to use ORDER BY on the
union
results to get the order I need. What I need at the end is an order
like:

Organization A - [blank field] - [blank field] - Geography A
Organization A - [blank field] - [blank field] - Geography B
Organization A - [blank field] - [blank field] - Geography C
---------------------------------------------------------------------
[end
of 1st query]
Organization O - [blank field] - [blank field] - [blank field]
Organization O - [SubOrg A] - [blank field] - [blank field]
Organization O - [SubOrg A] - [Group A] - [blank field]
Organization O - [SubOrg A] - [Group B] - [blank field]
Organization O - [SubOrg A] - [Group C] - [blank field]
Organization O - [SubOrg B] - [blank field] - [blank field]
Organization O - [SubOrg C] - [blank field] - [blank field]
---------------------------------------------------------------------
[end
of 2nd query]
Organization M - [blank field] - [blank field] - [blank field]
Organization M - [SubOrg A] - [blank field] - [blank field]
Organization M - [SubOrg B] - [blank field] - [blank field]
Organization M - [SubOrg C] - [blank field] - [blank field]
---------------------------------------------------------------------
[end
of 3rd query]
Organization C - [blank field] - [blank field] - [blank field]
Organization D - [blank field] - [blank field] - [blank field]
Organization E - [blank field] - [blank field] - [blank field]
---------------------------------------------------------------------
[end
of 4th query]
etc...

I can't really create a static table (or don't know how) that lists
the
proper sort order for these results, which I could then link to the
union
results to sort after the fact. The organizations, etc. in this data
will
change over time and it would be difficult to maintain a static
table. Any
advice?

Thanks
rachael

 

[rachael]

Ok, i think i get it. I'll give it a try.

Thanks, David and geebee!
rachael

You can treat the UNION query as though it were a table, and SELECT and
ORDER BY
You will have a bit of work with field names, alas.

FWIW this worked:

SELECT pets AS [pets_], name1 as [name1_]
FROM
(SELECT Pets, name1 FROM Table1)
UNION
(SELECT location AS Pets, Name1 FROM Table1)
ORDER BY [Pets_];

I have a union query that is combining the results of several other
queries.
I have a specific sort order that I need to maintain - this sort order is
built into the individual queries, but not the union query that joins them
together. The problem is there is no easy way to use ORDER BY on the union
results to get the order I need. What I need at the end is an order like:

Organization A - [blank field] - [blank field] - Geography A
Organization A - [blank field] - [blank field] - Geography B
Organization A - [blank field] - [blank field] - Geography C
--------------------------------------------------------------------- [end
of 1st query]
Organization O - [blank field] - [blank field] - [blank field]
Organization O - [SubOrg A] - [blank field] - [blank field]
Organization O - [SubOrg A] - [Group A] - [blank field]
Organization O - [SubOrg A] - [Group B] - [blank field]
Organization O - [SubOrg A] - [Group C] - [blank field]
Organization O - [SubOrg B] - [blank field] - [blank field]
Organization O - [SubOrg C] - [blank field] - [blank field]
--------------------------------------------------------------------- [end
of 2nd query]
Organization M - [blank field] - [blank field] - [blank field]
Organization M - [SubOrg A] - [blank field] - [blank field]
Organization M - [SubOrg B] - [blank field] - [blank field]
Organization M - [SubOrg C] - [blank field] - [blank field]
--------------------------------------------------------------------- [end
of 3rd query]
Organization C - [blank field] - [blank field] - [blank field]
Organization D - [blank field] - [blank field] - [blank field]
Organization E - [blank field] - [blank field] - [blank field]
--------------------------------------------------------------------- [end
of 4th query]
etc...

I can't really create a static table (or don't know how) that lists the
proper sort order for these results, which I could then link to the union
results to sort after the fact. The organizations, etc. in this data will
change over time and it would be difficult to maintain a static table. Any
advice?

Thanks
rachael

 

[John Vinson]

I have a union query that is combining the results of several other queries.
I have a specific sort order that I need to maintain – this sort order is
built into the individual queries, but not the union query that joins them
together. The problem is there is no easy way to use ORDER BY on the union
results to get the order I need. What I need at the end is an order like:

NOt sure it will work, but try using UNION ALL instead of UNION. It's
a certainty that UNION resorts the records (to remove duplicates); if
you don't have duplicates that need to be removed, UNION ALL will skip
this step. I suspect that it will also present the record order
unchanged, but try it and see.

John W. Vinson[MVP]

 

[rachael]

Ok, I still need help with this one. I understand using the UNION query as
the basis for a SELECT query, but the sorting is still a problem. The issue
is that I can't use a simple alphabetic sort within a single field to get the
results I need.

The final results need to look like

T_Organization - [blank field] - [blank field] - [blank field] - Count
T_Organization - [blank field] - [blank field] - A_Geography - Count
T_Organization - [blank field] - [blank field] - B_Geography - Count
T_Organization - [blank field] - [blank field] - C_Geography - Count
O_Organization - [blank field] - [blank field] - [blank field] - Count
O_Organization - C_SubOrg - [blank field] - [blank field] - Count
O_Organization - C_SubOrg - A_SubOrg2 - [blank field] - Count
O_Organization - C_SubOrg - B_SubOrg2 - [blank field] - Count
P_Organization - [blank field] - [blank field] - [blank field] - Count
P_Organization - G_SubOrg - [blank field] - [blank field] - Count
P_Organization - G_SubOrg - A_SubOrg2 - [blank field] - Count
P_Organization - G_SubOrg - B_SubOrg2 - [blank field] - Count

Because the results are being used by another group, I can't simply add a
blank in front of the T_Organization's name to ensure it gets sorted to the
top of the list. I really do need that first field's results sorted so T_Org
is first, O_Org is second, and P_Org is third. In other words, out of
alphabetic order, both ascending and descending.

That's where I'm unclear. If you still need me to post the UNION query, I
will, but the parts are just to select the T_Org results, union the O_Org
results, union the P_Org results.

Thanks
rachael

hi,

The UNION QUERY loses the desired sort order maintained by its individual
comprising queries, but your new SELECT QUERY with the UNION QUERY being the
"query source" should re-sort your results as desired. If you post the UNION
QUERY, I can suggest a new query.

geebee

Thanks, geebee, but i don't see how that will help. The data are sorted
within each query used in the union, but the overall results aren't sorted in
a simple way. For instance, you'll see below that the first group of Org
results starts with A, the second starts with O, the third starts with M,
etc. That's the order i need to maintain.

Unless i'm missing something with your suggestion, which is always possible.


rachael

Hi,

Create a SELECT query, based on your stored UNION QUERY. With that query,
you can select all the columns from your UNION QUERY and sort accordingly.
This would eliminate the ned to create any kind of table. Each time you run
the SELECT QUERY with the UNION QUERY as the source for columns, the UNION
QUERY would in turn/therfore be updated.

geebee


:

I have a union query that is combining the results of several other queries.
I have a specific sort order that I need to maintain – this sort order is
built into the individual queries, but not the union query that joins them
together. The problem is there is no easy way to use ORDER BY on the union
results to get the order I need. What I need at the end is an order like:

Organization A – [blank field] – [blank field] – Geography A
Organization A – [blank field] – [blank field] – Geography B
Organization A – [blank field] – [blank field] – Geography C
--------------------------------------------------------------------- [end
of 1st query]
Organization O – [blank field] – [blank field] – [blank field]
Organization O – [SubOrg A] – [blank field] – [blank field]
Organization O – [SubOrg A] – [Group A] – [blank field]
Organization O – [SubOrg A] – [Group B] – [blank field]
Organization O – [SubOrg A] – [Group C] – [blank field]
Organization O – [SubOrg B] – [blank field] – [blank field]
Organization O – [SubOrg C] – [blank field] – [blank field]
--------------------------------------------------------------------- [end
of 2nd query]
Organization M – [blank field] – [blank field] – [blank field]
Organization M – [SubOrg A] – [blank field] – [blank field]
Organization M – [SubOrg B] – [blank field] – [blank field]
Organization M – [SubOrg C] – [blank field] – [blank field]
--------------------------------------------------------------------- [end
of 3rd query]
Organization C – [blank field] – [blank field] – [blank field]
Organization D – [blank field] – [blank field] – [blank field]
Organization E – [blank field] – [blank field] – [blank field]
--------------------------------------------------------------------- [end
of 4th query]
etc...

I can’t really create a static table (or don’t know how) that lists the
proper sort order for these results, which I could then link to the union
results to sort after the fact. The organizations, etc. in this data will
change over time and it would be difficult to maintain a static table. Any
advice?

Thanks
rachael