By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 1,927 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Sort by a second field in a SELECT TOP query

P: n/a
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
Share this Question
Share on Google+
5 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
"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.