Top 20 and bottom 20 values

You might try a UNION query whose SQL looks something like this:

SELECT
*
FROM
(SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field])
UNION ALL
SELECT
*
FROM
(SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field] DESC);

If you're using Access 97 or earlier, you need to replace subqueries
(enclosed in parentheses) with separate saved queries.

Only the last subquery is actually necessary. It is needed to associate the
ORDER by clause with the last SELECT. Otherwise, an ORDER BY clause would
apply to the entire result (of the UNION), and you would not get the TOP 20.

"Gwen" wrote in message
...
Hi,

I would like to get the top 20 values and bottom 20 values in 1 query.
If possible, please direct.


Thanks

Gwen

Hi,

I would like to get the top 20 values and bottom 20 values in 1 query.
If possible, please direct.


Thanks

Gwen

Brian

I tend to work more with the query designer than with raw SQL. When I tried
to "solve" the issue presented, I found that I didn't need the "wrapper" SQL
statements (i.e. "SELECT * FROM (...)"). I was able, totally within Access,
to UNION together two "SELECT TOP 20 ..." SQL statements and generate a list
of 40.

I'm always looking to expand my understanding -- can you elaborate on the
purpose of the "wrappers"?

Thanks!

Jeff Boyce
Access MVP

"Brian Camire" wrote in message
...
You might try a UNION query whose SQL looks something like this:

SELECT
*
FROM
(SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field])
UNION ALL
SELECT
*
FROM
(SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field] DESC);

If you're using Access 97 or earlier, you need to replace subqueries
(enclosed in parentheses) with separate saved queries.

Only the last subquery is actually necessary. It is needed to associate
the
ORDER by clause with the last SELECT. Otherwise, an ORDER BY clause would
apply to the entire result (of the UNION), and you would not get the TOP
20.

"Gwen" wrote in message
...
Hi,

I would like to get the top 20 values and bottom 20 values in 1 query.
If possible, please direct.


Thanks

Gwen

I'm not sure if I understand your question, but...

What I was trying to say was that you can't just use something like:

SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field]
UNION ALL
SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field] DESC;

because the last ORDER BY clause will be applied to the UNION-ed results and
not the last SELECT. Because the last SELECT will not have an ORDER BY, the
records it returns (the TOP 20) will arbitrarily selected. In other words,
you at least need something like this:

SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field]
UNION ALL
SELECT
*
FROM
(SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field] DESC);

If you used saved queries, say one named "Top 20 Records" like this:

SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field];

and another, say named "Bottom 20 Records" like this:

SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field] DESC;

("top" and "bottom" here are relative) then the UNION query might look
something like this:

SELECT
[Top 20 Records].*
FROM
[Top 20 Records]
UNION ALL
SELECT
[Bottom 20 Records].*
FROM
[Bottom 20 Records];

Is that what you did?

I'm on Access 2000, where you can't create UNION queries in design view.
Can you do this in Access XP?


"Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message
...
Brian

I tend to work more with the query designer than with raw SQL. When I
tried
to "solve" the issue presented, I found that I didn't need the "wrapper"
SQL
statements (i.e. "SELECT * FROM (...)"). I was able, totally within
Access,
to UNION together two "SELECT TOP 20 ..." SQL statements and generate a
list
of 40.

I'm always looking to expand my understanding -- can you elaborate on the
purpose of the "wrappers"?

Thanks!

Jeff Boyce
Access MVP

"Brian Camire" wrote in message
...
You might try a UNION query whose SQL looks something like this:

SELECT
*
FROM
(SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field])
UNION ALL
SELECT
*
FROM
(SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field] DESC);

If you're using Access 97 or earlier, you need to replace subqueries
(enclosed in parentheses) with separate saved queries.

Only the last subquery is actually necessary. It is needed to associate
the
ORDER by clause with the last SELECT. Otherwise, an ORDER BY clause
would
apply to the entire result (of the UNION), and you would not get the TOP
20.

"Gwen" wrote in message
...
Hi,

I would like to get the top 20 values and bottom 20 values in 1 query.
If possible, please direct.


Thanks

Gwen

So, this is going to get weird!

I tried

SELECT TOP 5 MyTable.MyField
FROM MyTable
ORDER BY
MyTable.MyField
UNION
SELECT TOP 5 MyTable.MyField
FROM MyTable
ORDER BY
MyTable.MyField DESC;

and only got 5...

BUT!!

When I tried

SELECT TOP 5 MyTable.MyField
FROM MyTable
ORDER BY
MyTable.MyField DESC
UNION
SELECT TOP 5 MyTable.MyField
FROM MyTable
ORDER BY
MyTable.MyField;

I got the 10 I expected!

?!SQL UNION queries are dependent on the order?!

Does this happen to you, too? (Access 2000, Windows 2000).

And I create it by opening a new query and entering the SQL statements
(copied from queries' SQL statements) in the SQL design mode, rather than
the standard query design mode.

Jeff Boyce
Access MVP

"Brian Camire" wrote in message
...
I'm not sure if I understand your question, but...

What I was trying to say was that you can't just use something like:

SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field]
UNION ALL
SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field] DESC;

because the last ORDER BY clause will be applied to the UNION-ed results
and
not the last SELECT. Because the last SELECT will not have an ORDER BY,
the
records it returns (the TOP 20) will arbitrarily selected. In other
words,
you at least need something like this:

SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field]
UNION ALL
SELECT
*
FROM
(SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field] DESC);

If you used saved queries, say one named "Top 20 Records" like this:

SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field];

and another, say named "Bottom 20 Records" like this:

SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field] DESC;

("top" and "bottom" here are relative) then the UNION query might look
something like this:

SELECT
[Top 20 Records].*
FROM
[Top 20 Records]
UNION ALL
SELECT
[Bottom 20 Records].*
FROM
[Bottom 20 Records];

Is that what you did?

I'm on Access 2000, where you can't create UNION queries in design view.
Can you do this in Access XP?


"Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message
...
Brian

I tend to work more with the query designer than with raw SQL. When I
tried
to "solve" the issue presented, I found that I didn't need the "wrapper"
SQL
statements (i.e. "SELECT * FROM (...)"). I was able, totally within
Access,
to UNION together two "SELECT TOP 20 ..." SQL statements and generate a
list
of 40.

I'm always looking to expand my understanding -- can you elaborate on
the
purpose of the "wrappers"?

Thanks!

Jeff Boyce
Access MVP

"Brian Camire" wrote in message
...
You might try a UNION query whose SQL looks something like this:

SELECT
*
FROM
(SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field])
UNION ALL
SELECT
*
FROM
(SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field] DESC);

If you're using Access 97 or earlier, you need to replace subqueries
(enclosed in parentheses) with separate saved queries.

Only the last subquery is actually necessary. It is needed to
associate
the
ORDER by clause with the last SELECT. Otherwise, an ORDER BY clause
would
apply to the entire result (of the UNION), and you would not get the
TOP
20.

"Gwen" wrote in message
...
Hi,

I would like to get the top 20 values and bottom 20 values in 1
query.
If possible, please direct.


Thanks

Gwen

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

UNION queries only use the last ORDER BY statement to order the
column(s) in each SELECT statement. It probably can be done using
subqueries:

SELECT *
FROM table
WHERE ID IN (SELECT TOP 20 ID FROM table )
OR ID IN (SELECT TOP 20 ID FROM table ORDER BY ID DESC)

--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQd387YechKqOuFEgEQLCXgCdHbOmduCixuzgV3I8iYmWZE xsQqQAoOH2
KE/s7Lhi6ACyWLL4NP3zSbWq
=aQxl
-----END PGP SIGNATURE-----


Jeff Boyce wrote:
So, this is going to get weird!

I tried

SELECT TOP 5 MyTable.MyField
FROM MyTable
ORDER BY
MyTable.MyField
UNION
SELECT TOP 5 MyTable.MyField
FROM MyTable
ORDER BY
MyTable.MyField DESC;

and only got 5...

BUT!!

When I tried

SELECT TOP 5 MyTable.MyField
FROM MyTable
ORDER BY
MyTable.MyField DESC
UNION
SELECT TOP 5 MyTable.MyField
FROM MyTable
ORDER BY
MyTable.MyField;

I got the 10 I expected!

?!SQL UNION queries are dependent on the order?!

Does this happen to you, too? (Access 2000, Windows 2000).

And I create it by opening a new query and entering the SQL statements
(copied from queries' SQL statements) in the SQL design mode, rather than
the standard query design mode.

Jeff Boyce
Access MVP

"Brian Camire" wrote in message
...

I'm not sure if I understand your question, but...

What I was trying to say was that you can't just use something like:

SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field]
UNION ALL
SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field] DESC;

because the last ORDER BY clause will be applied to the UNION-ed results

and

not the last SELECT. Because the last SELECT will not have an ORDER BY,

the

records it returns (the TOP 20) will arbitrarily selected. In other

words,

you at least need something like this:

SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field]
UNION ALL
SELECT
*
FROM
(SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field] DESC);

If you used saved queries, say one named "Top 20 Records" like this:

SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field];

and another, say named "Bottom 20 Records" like this:

SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field] DESC;

("top" and "bottom" here are relative) then the UNION query might look
something like this:

SELECT
[Top 20 Records].*
FROM
[Top 20 Records]
UNION ALL
SELECT
[Bottom 20 Records].*
FROM
[Bottom 20 Records];

Is that what you did?

I'm on Access 2000, where you can't create UNION queries in design view.
Can you do this in Access XP?


"Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message
.. .

Brian

I tend to work more with the query designer than with raw SQL. When I

tried

to "solve" the issue presented, I found that I didn't need the "wrapper"

SQL

statements (i.e. "SELECT * FROM (...)"). I was able, totally within

Access,

to UNION together two "SELECT TOP 20 ..." SQL statements and generate a

list

of 40.

I'm always looking to expand my understanding -- can you elaborate on

the

purpose of the "wrappers"?

Thanks!

Jeff Boyce
Access MVP

"Brian Camire" wrote in message
...

You might try a UNION query whose SQL looks something like this:

SELECT
*
FROM
(SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field])
UNION ALL
SELECT
*
FROM
(SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field] DESC);

If you're using Access 97 or earlier, you need to replace subqueries
(enclosed in parentheses) with separate saved queries.

Only the last subquery is actually necessary. It is needed to

associate

the

ORDER by clause with the last SELECT. Otherwise, an ORDER BY clause

would

apply to the entire result (of the UNION), and you would not get the

TOP

20.

"Gwen" wrote in message
.. .

Hi,

I would like to get the top 20 values and bottom 20 values in 1

query.

If possible, please direct.


Thanks

Gwen

For the record (as posted in TekTips)
Consider a query of the Orders table in Northwind with the top 20 and bottom
20 OrderID values:

SELECT Orders.*
FROM Orders
WHERE Orders.OrderID In
(SELECT TOP 20 OrderID
FROM Orders
ORDER BY OrderID)
Or Orders.OrderID In
(SELECT TOP 20 OrderID
FROM Orders
ORDER BY OrderID Desc);

--
Duane Hookom
MS Access MVP


"Gwen" wrote in message
...
Hi,

I would like to get the top 20 values and bottom 20 values in 1 query.
If possible, please direct.


Thanks

Gwen

I had recalled that the last ORDER BY is the one used by UNION queries, but
my two versions produce different results. One gathers both the top and
bottom values, the other only one set.

Got any leads on that?

Jeff Boyce
Access MVP

"MGFoster" wrote in message
ink.net...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

UNION queries only use the last ORDER BY statement to order the
column(s) in each SELECT statement. It probably can be done using
subqueries:

SELECT *
FROM table
WHERE ID IN (SELECT TOP 20 ID FROM table )
OR ID IN (SELECT TOP 20 ID FROM table ORDER BY ID DESC)

--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQd387YechKqOuFEgEQLCXgCdHbOmduCixuzgV3I8iYmWZE xsQqQAoOH2
KE/s7Lhi6ACyWLL4NP3zSbWq
=aQxl
-----END PGP SIGNATURE-----


Jeff Boyce wrote:
So, this is going to get weird!

I tried

SELECT TOP 5 MyTable.MyField
FROM MyTable
ORDER BY
MyTable.MyField
UNION
SELECT TOP 5 MyTable.MyField
FROM MyTable
ORDER BY
MyTable.MyField DESC;

and only got 5...

BUT!!

When I tried

SELECT TOP 5 MyTable.MyField
FROM MyTable
ORDER BY
MyTable.MyField DESC
UNION
SELECT TOP 5 MyTable.MyField
FROM MyTable
ORDER BY
MyTable.MyField;

I got the 10 I expected!

?!SQL UNION queries are dependent on the order?!

Does this happen to you, too? (Access 2000, Windows 2000).

And I create it by opening a new query and entering the SQL statements
(copied from queries' SQL statements) in the SQL design mode, rather
than
the standard query design mode.

Jeff Boyce
Access MVP

"Brian Camire" wrote in message
...

I'm not sure if I understand your question, but...

What I was trying to say was that you can't just use something like:

SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field]
UNION ALL
SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field] DESC;

because the last ORDER BY clause will be applied to the UNION-ed results

and

not the last SELECT. Because the last SELECT will not have an ORDER BY,

the

records it returns (the TOP 20) will arbitrarily selected. In other

words,

you at least need something like this:

SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field]
UNION ALL
SELECT
*
FROM
(SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field] DESC);

If you used saved queries, say one named "Top 20 Records" like this:

SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field];

and another, say named "Bottom 20 Records" like this:

SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field] DESC;

("top" and "bottom" here are relative) then the UNION query might look
something like this:

SELECT
[Top 20 Records].*
FROM
[Top 20 Records]
UNION ALL
SELECT
[Bottom 20 Records].*
FROM
[Bottom 20 Records];

Is that what you did?

I'm on Access 2000, where you can't create UNION queries in design view.
Can you do this in Access XP?


"Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in
message
.. .

Brian

I tend to work more with the query designer than with raw SQL. When I

tried

to "solve" the issue presented, I found that I didn't need the
"wrapper"

SQL

statements (i.e. "SELECT * FROM (...)"). I was able, totally within

Access,

to UNION together two "SELECT TOP 20 ..." SQL statements and generate a

list

of 40.

I'm always looking to expand my understanding -- can you elaborate on

the

purpose of the "wrappers"?

Thanks!

Jeff Boyce
Access MVP

"Brian Camire" wrote in message
...

You might try a UNION query whose SQL looks something like this:

SELECT
*
FROM
(SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field])
UNION ALL
SELECT
*
FROM
(SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field] DESC);

If you're using Access 97 or earlier, you need to replace subqueries
(enclosed in parentheses) with separate saved queries.

Only the last subquery is actually necessary. It is needed to

associate

the

ORDER by clause with the last SELECT. Otherwise, an ORDER BY clause

would

apply to the entire result (of the UNION), and you would not get the

TOP

20.

"Gwen" wrote in message
.. .

Hi,

I would like to get the top 20 values and bottom 20 values in 1

query.

If possible, please direct.


Thanks

Gwen

I expect what you are observing is due the the following:

1. You used UNION (instead of UNION ALL), which returns no duplicates
records.

2. By coincidence, the SELECT TOP without the ORDER BY (that is, the second
SELECT in the both queries) traversed rows in ascending order of MyField
(for example, perhaps because rows in MyTable were inserted in ascending
order of MyField).

To illustrate, suppose you had inserted the numbers 1 through 20 in
ascending order into MyTable.

In the first query, the

SELECT TOP 5 MyTable.MyField
FROM MyTable
ORDER BY
MyTable.MyField

would return the numbers 1 through 5, and the

SELECT TOP 5 MyTable.MyField
FROM MyTable

would also return the numbers 1 through 5 (assuming item 2 above).

UNION-ing the result would return the numbers 1 through 5, with no
duplicates, and the final ORDER BY would return them in descending order.

In the second query, the

SELECT TOP 5 MyTable.MyField
FROM MyTable
ORDER BY
MyTable.MyField DESC

would return the numbers 16 through 20, and the

SELECT TOP 5 MyTable.MyField
FROM MyTable

would return the numbers 1 through 5 (again, assuming item 2 above).

Since there would be no duplicates, UNION-ing the result would return the
numbers 1 through 5 and 16 through 20, and the final ORDER BY would return
them in ascending order.

Duane's solution is certainly less ambiguous.

"Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message
...
So, this is going to get weird!

I tried

SELECT TOP 5 MyTable.MyField
FROM MyTable
ORDER BY
MyTable.MyField
UNION
SELECT TOP 5 MyTable.MyField
FROM MyTable
ORDER BY
MyTable.MyField DESC;

and only got 5...

BUT!!

When I tried

SELECT TOP 5 MyTable.MyField
FROM MyTable
ORDER BY
MyTable.MyField DESC
UNION
SELECT TOP 5 MyTable.MyField
FROM MyTable
ORDER BY
MyTable.MyField;

I got the 10 I expected!

?!SQL UNION queries are dependent on the order?!

Does this happen to you, too? (Access 2000, Windows 2000).

And I create it by opening a new query and entering the SQL statements
(copied from queries' SQL statements) in the SQL design mode, rather than
the standard query design mode.

Jeff Boyce
Access MVP

"Brian Camire" wrote in message
...
I'm not sure if I understand your question, but...

What I was trying to say was that you can't just use something like:

SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field]
UNION ALL
SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field] DESC;

because the last ORDER BY clause will be applied to the UNION-ed results
and
not the last SELECT. Because the last SELECT will not have an ORDER BY,
the
records it returns (the TOP 20) will arbitrarily selected. In other
words,
you at least need something like this:

SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field]
UNION ALL
SELECT
*
FROM
(SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field] DESC);

If you used saved queries, say one named "Top 20 Records" like this:

SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field];

and another, say named "Bottom 20 Records" like this:

SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field] DESC;

("top" and "bottom" here are relative) then the UNION query might look
something like this:

SELECT
[Top 20 Records].*
FROM
[Top 20 Records]
UNION ALL
SELECT
[Bottom 20 Records].*
FROM
[Bottom 20 Records];

Is that what you did?

I'm on Access 2000, where you can't create UNION queries in design view.
Can you do this in Access XP?


"Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in
message
...
Brian

I tend to work more with the query designer than with raw SQL. When I
tried
to "solve" the issue presented, I found that I didn't need the
"wrapper"
SQL
statements (i.e. "SELECT * FROM (...)"). I was able, totally within
Access,
to UNION together two "SELECT TOP 20 ..." SQL statements and generate
a
list
of 40.

I'm always looking to expand my understanding -- can you elaborate on
the
purpose of the "wrappers"?

Thanks!

Jeff Boyce
Access MVP

"Brian Camire" wrote in message
...
You might try a UNION query whose SQL looks something like this:

SELECT
*
FROM
(SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field])
UNION ALL
SELECT
*
FROM
(SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field] DESC);

If you're using Access 97 or earlier, you need to replace subqueries
(enclosed in parentheses) with separate saved queries.

Only the last subquery is actually necessary. It is needed to
associate
the
ORDER by clause with the last SELECT. Otherwise, an ORDER BY clause
would
apply to the entire result (of the UNION), and you would not get the
TOP
20.

"Gwen" wrote in message
...
Hi,

I would like to get the top 20 values and bottom 20 values in 1
query.
If possible, please direct.


Thanks

Gwen

That makes sense... Thanks!

Jeff

"Brian Camire" wrote in message
...
I expect what you are observing is due the the following:

1. You used UNION (instead of UNION ALL), which returns no duplicates
records.

2. By coincidence, the SELECT TOP without the ORDER BY (that is, the
second
SELECT in the both queries) traversed rows in ascending order of MyField
(for example, perhaps because rows in MyTable were inserted in ascending
order of MyField).

To illustrate, suppose you had inserted the numbers 1 through 20 in
ascending order into MyTable.

In the first query, the

SELECT TOP 5 MyTable.MyField
FROM MyTable
ORDER BY
MyTable.MyField

would return the numbers 1 through 5, and the

SELECT TOP 5 MyTable.MyField
FROM MyTable

would also return the numbers 1 through 5 (assuming item 2 above).

UNION-ing the result would return the numbers 1 through 5, with no
duplicates, and the final ORDER BY would return them in descending order.

In the second query, the

SELECT TOP 5 MyTable.MyField
FROM MyTable
ORDER BY
MyTable.MyField DESC

would return the numbers 16 through 20, and the

SELECT TOP 5 MyTable.MyField
FROM MyTable

would return the numbers 1 through 5 (again, assuming item 2 above).

Since there would be no duplicates, UNION-ing the result would return the
numbers 1 through 5 and 16 through 20, and the final ORDER BY would return
them in ascending order.

Duane's solution is certainly less ambiguous.

"Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message
...
So, this is going to get weird!

I tried

SELECT TOP 5 MyTable.MyField
FROM MyTable
ORDER BY
MyTable.MyField
UNION
SELECT TOP 5 MyTable.MyField
FROM MyTable
ORDER BY
MyTable.MyField DESC;

and only got 5...

BUT!!

When I tried

SELECT TOP 5 MyTable.MyField
FROM MyTable
ORDER BY
MyTable.MyField DESC
UNION
SELECT TOP 5 MyTable.MyField
FROM MyTable
ORDER BY
MyTable.MyField;

I got the 10 I expected!

?!SQL UNION queries are dependent on the order?!

Does this happen to you, too? (Access 2000, Windows 2000).

And I create it by opening a new query and entering the SQL statements
(copied from queries' SQL statements) in the SQL design mode, rather
than
the standard query design mode.

Jeff Boyce
Access MVP

"Brian Camire" wrote in message
...
I'm not sure if I understand your question, but...

What I was trying to say was that you can't just use something like:

SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field]
UNION ALL
SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field] DESC;

because the last ORDER BY clause will be applied to the UNION-ed
results
and
not the last SELECT. Because the last SELECT will not have an ORDER
BY,
the
records it returns (the TOP 20) will arbitrarily selected. In other
words,
you at least need something like this:

SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field]
UNION ALL
SELECT
*
FROM
(SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field] DESC);

If you used saved queries, say one named "Top 20 Records" like this:

SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field];

and another, say named "Bottom 20 Records" like this:

SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field] DESC;

("top" and "bottom" here are relative) then the UNION query might look
something like this:

SELECT
[Top 20 Records].*
FROM
[Top 20 Records]
UNION ALL
SELECT
[Bottom 20 Records].*
FROM
[Bottom 20 Records];

Is that what you did?

I'm on Access 2000, where you can't create UNION queries in design
view.
Can you do this in Access XP?


"Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in
message
...
Brian

I tend to work more with the query designer than with raw SQL. When
I
tried
to "solve" the issue presented, I found that I didn't need the
"wrapper"
SQL
statements (i.e. "SELECT * FROM (...)"). I was able, totally within
Access,
to UNION together two "SELECT TOP 20 ..." SQL statements and
generate
a
list
of 40.

I'm always looking to expand my understanding -- can you elaborate
on
the
purpose of the "wrappers"?

Thanks!

Jeff Boyce
Access MVP

"Brian Camire" wrote in message
...
You might try a UNION query whose SQL looks something like this:

SELECT
*
FROM
(SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field])
UNION ALL
SELECT
*
FROM
(SELECT TOP 20
[Your Table].*
FROM
[Your Table]
ORDER BY
[Your Table].[Your Ordering Field] DESC);

If you're using Access 97 or earlier, you need to replace
subqueries
(enclosed in parentheses) with separate saved queries.

Only the last subquery is actually necessary. It is needed to
associate
the
ORDER by clause with the last SELECT. Otherwise, an ORDER BY
clause
would
apply to the entire result (of the UNION), and you would not get
the
TOP
20.

"Gwen" wrote in message
...
Hi,

I would like to get the top 20 values and bottom 20 values in 1
query.
If possible, please direct.


Thanks

Gwen