469,644 Members | 1,959 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,644 developers. It's quick & easy.

Sort by a second field in a SELECT TOP query

The following query will give me the top ten states with the most callers.

SELECT TOP 10 Count(Callers.CallerID) AS CountOfCallerID, Callers.State
FROM Callers
GROUP BY Callers.State
ORDER BY Count(Callers.CallerID) DESC;

How can I return the same result set, the top ten states, but sort the
result set by state.

Thanks for any help.
Nov 13 '05 #1
5 6455
Simplest way is to use this query as the datasoure for another one that
sorts by State.

Larry Linson
Microsoft Access MVP

"Terri" <Te***@spamaway.com> wrote in message
news:ch**********@reader2.nmix.net...
The following query will give me the top ten states with the most callers.

SELECT TOP 10 Count(Callers.CallerID) AS CountOfCallerID, Callers.State
FROM Callers
GROUP BY Callers.State
ORDER BY Count(Callers.CallerID) DESC;

How can I return the same result set, the top ten states, but sort the
result set by state.

Thanks for any help.

Nov 13 '05 #2
Fair enough. Any way with just one query. I'm building SELECT statements
dynamically in a reporting application
Thanks
"Larry Linson" <bo*****@localhost.not> wrote in message
news:76PZc.5371$_%3.4643@trnddc06...
Simplest way is to use this query as the datasoure for another one that
sorts by State.

Larry Linson
Microsoft Access MVP

"Terri" <Te***@spamaway.com> wrote in message
news:ch**********@reader2.nmix.net...
The following query will give me the top ten states with the most callers.
SELECT TOP 10 Count(Callers.CallerID) AS CountOfCallerID, Callers.State
FROM Callers
GROUP BY Callers.State
ORDER BY Count(Callers.CallerID) DESC;

How can I return the same result set, the top ten states, but sort the
result set by state.

Thanks for any help.


Nov 13 '05 #3
Terri wrote:
Fair enough. Any way with just one query. I'm building SELECT statements
dynamically in a reporting application
Thanks
"Larry Linson" <bo*****@localhost.not> wrote in message
news:76PZc.5371$_%3.4643@trnddc06...
Simplest way is to use this query as the datasoure for another one that
sorts by State.

Larry Linson
Microsoft Access MVP

"Terri" <Te***@spamaway.com> wrote in message
news:ch**********@reader2.nmix.net...
The following query will give me the top ten states with the most
callers.
SELECT TOP 10 Count(Callers.CallerID) AS CountOfCallerID, Callers.State
FROM Callers
GROUP BY Callers.State
ORDER BY Count(Callers.CallerID) DESC;

How can I return the same result set, the top ten states, but sort the
result set by state.

Thanks for any help.


Usually you sort by using the Sorting/Grouping dialog in a report. You
can also use the OrderBy and OrderByOn properties of a report to set the
sort order.

IOW, remove the sort order in the query (since sorting is irrelevent as
a report's recordsource).

If your problem is not in regards to a report, ignore this post.
Nov 13 '05 #4
Salad's reply is correct, except in one respect: the sort order of the data
in the RecordSource is ignored in a report, and only the sorting and
grouping in the Report's Sorting and Grouping property used to control the
display.

However, in your case, the ORDER BY is necessary to choose the proper TOP
values.

Larry Linson
Microsoft Access MVP

"Salad" <oi*@vinegar.com> wrote in message
news:AF***************@newsread3.news.pas.earthlin k.net...
Terri wrote:
Fair enough. Any way with just one query. I'm building SELECT statements
dynamically in a reporting application
Thanks
"Larry Linson" <bo*****@localhost.not> wrote in message
news:76PZc.5371$_%3.4643@trnddc06...
Simplest way is to use this query as the datasoure for another one that
sorts by State.

Larry Linson
Microsoft Access MVP

"Terri" <Te***@spamaway.com> wrote in message
news:ch**********@reader2.nmix.net...

The following query will give me the top ten states with the most


callers.
SELECT TOP 10 Count(Callers.CallerID) AS CountOfCallerID, Callers.State
FROM Callers
GROUP BY Callers.State
ORDER BY Count(Callers.CallerID) DESC;

How can I return the same result set, the top ten states, but sort the
result set by state.

Thanks for any help.


Usually you sort by using the Sorting/Grouping dialog in a report. You
can also use the OrderBy and OrderByOn properties of a report to set the
sort order.

IOW, remove the sort order in the query (since sorting is irrelevent as
a report's recordsource).

If your problem is not in regards to a report, ignore this post.

Nov 13 '05 #5
"Terri" <Te***@spamaway.com> wrote in message news:<ch**********@reader2.nmix.net>...
The following query will give me the top ten states with the most callers.

SELECT TOP 10 Count(Callers.CallerID) AS CountOfCallerID, Callers.State
FROM Callers
GROUP BY Callers.State
ORDER BY Count(Callers.CallerID) DESC;

How can I return the same result set, the top ten states, but sort the
result set by state.


Use this query as the source for another query:

SELECT *
FROM (SELECT TOP 10 Count(Callers.CallerID) AS CountOfCallerID, Callers.State
FROM Callers
GROUP BY Callers.State
ORDER BY Count(Callers.CallerID) DESC)
ORDER BY Callers.State ;
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by A. Respecte | last post: by
3 posts views Thread by beconrad | last post: by
5 posts views Thread by muskie | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.