Top 20 and bottom 20 values

  • Thread starter Thread starter Brian Camire
  • Start date Start date
B

Brian Camire

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.
 
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 said:
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 said:
Hi,

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


Thanks

Gwen
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
Hi,

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


Thanks

Gwen
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
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
Click to expand...
TOP
20.
Hi,

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


Thanks

Gwen
Click to expand...
Click to expand...
Click to expand...
 
-----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/AwUBQd387YechKqOuFEgEQLCXgCdHbOmduCixuzgV3I8iYmWZExsQqQAoOH2
KE/s7Lhi6ACyWLL4NP3zSbWq
=aQxl
-----END PGP SIGNATURE-----


Jeff said:
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>

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?


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
Click to expand...
the
purpose of the "wrappers"?

Thanks!

Jeff Boyce
<Access MVP>


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
Click to expand...
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
Click to expand...
TOP
20.


Hi,

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


Thanks

Gwen
Click to expand...
Click to expand...
Click to expand...
 
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);
 
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 said:
-----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/AwUBQd387YechKqOuFEgEQLCXgCdHbOmduCixuzgV3I8iYmWZExsQqQAoOH2
KE/s7Lhi6ACyWLL4NP3zSbWq
=aQxl
-----END PGP SIGNATURE-----


Jeff said:
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>

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?



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>


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.


Hi,

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

If possible, please direct.


Thanks

Gwen
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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?


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
Click to expand...
"wrapper"
SQL
statements (i.e. "SELECT * FROM (...)"). I was able, totally within Access,
to UNION together two "SELECT TOP 20 ..." SQL statements and generate
Click to expand...
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>

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.

Hi,

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


Thanks

Gwen
Click to expand...
Click to expand...
Click to expand...
 
That makes sense... Thanks!

Jeff

Brian Camire said:
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 said:
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 said:
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
Click to expand...
results
and
not the last SELECT. Because the last SELECT will not have an ORDER
Click to expand...
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?


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
Click to expand...
Click to expand...
generate
a
list
of 40.

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

Thanks!

Jeff Boyce
<Access MVP>

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
Click to expand...
the
TOP
20.

Hi,

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


Thanks

Gwen
Click to expand...
Click to expand...
Click to expand...
 
Back
Top