Sort by a second field in a SELECT TOP query

[Terri]

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.

[Larry Linson]

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.

[Terri]

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.

[Salad]

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.

[Larry Linson]

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.

[almish]

"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 ;