Help with sql Statement

[MC D]

I have a table of the most commonly incorrectly answered questions in my
testing application. Imagine the one column table looks like:

a
a
a
b
b
b
b
c
c
d

(so b is the most common, d is the least common). What I want to do is
retrieve the top 3 most commonly appearing questions in this table. If I do:

"Select Top 3 answers from incorrectAnswers"

The dataset looks like:

a
a
a

That makes no sense to me, it should be:

b
a
c

I thought that maybe the returned records are each individual record,
unordered, but if I say,

"Select Top 3 answers from incorrectAnswers Group By answers", I get:

a
b
c

Huh? Is it defaulting to ordering alphabetically? How can I order by the
"top" records?

Thanks!
D

[Armin Zingler]

"MC D" <as***@earthtalk.com> schrieb

I have a table of the most commonly incorrectly answered questions in
my testing application. Imagine the one column table looks like:

This is a VB.NET language group. Please turn to a group dealing with SQL
questions, e.g. microsoft.public.dotnet.framework.adonet
--
Armin

[Larry Woods]

Top X just gives you the top X records, depending on your sort order, or
lack of it.

Try something like this:

SELECT TOP 3 Answers,Count(Answers) as TotCount FROM (SELECT Answers FROM
IncorrectAnswers GROUP BY Answers
ORDER BY Count(Answers) DESC);

I am assuming that IncorrectAnswers is the table, and Answers is the field.
You probably don't need the Count returned, but you could use that to verify
that everything is O.K.

HTH,

Larry Woods

"MC D" <as***@earthtalk.com> wrote in message
news:uc**************@TK2MSFTNGP10.phx.gbl...

I have a table of the most commonly incorrectly answered questions in my
testing application. Imagine the one column table looks like:

a
a
a
b
b
b
b
c
c
d

(so b is the most common, d is the least common). What I want to do is
retrieve the top 3 most commonly appearing questions in this table. If I do:
"Select Top 3 answers from incorrectAnswers"

The dataset looks like:

a
a
a

That makes no sense to me, it should be:

b
a
c

I thought that maybe the returned records are each individual record,
unordered, but if I say,

"Select Top 3 answers from incorrectAnswers Group By answers", I get:

a
b
c

Huh? Is it defaulting to ordering alphabetically? How can I order by the
"top" records?

Thanks!
D

[Herfried K. Wagner [MVP]]

Hello,

"MC D" <as***@earthtalk.com> schrieb:

I have a table of the most commonly incorrectly answered
questions in my testing application. Imagine the one column
table looks like:

This is a VB.NET language group. Please turn to the ADO.NET newsgroup:

news://msnews.microsoft.com/microsof...amework.adonet

Web interface:

http://msdn.microsoft.com/newsgroups...amework.adonet

Regards,
Herfried K. Wagner
--
MVP · VB Classic, VB.NET
http://www.mvps.org/dotnet

[MC D]

That works with the exception of the first aggregate function. The
Count(Answers) as TotCount generates an error. "You attempted to execute a
query that does not include the specified expression "Answers" as part of an
aggregate function."

I would like to be able to to retrieve this information without having to
run a sub query for each one. Any further ideas?

Thanks a million!

"Larry Woods" <la***@lwoods.com> wrote in message
news:ef**************@TK2MSFTNGP12.phx.gbl...

Top X just gives you the top X records, depending on your sort order, or
lack of it.

Try something like this:

SELECT TOP 3 Answers,Count(Answers) as TotCount FROM (SELECT Answers FROM
IncorrectAnswers GROUP BY Answers
ORDER BY Count(Answers) DESC);

I am assuming that IncorrectAnswers is the table, and Answers is the field. You probably don't need the Count returned, but you could use that to verify that everything is O.K.

HTH,

Larry Woods

"MC D" <as***@earthtalk.com> wrote in message
news:uc**************@TK2MSFTNGP10.phx.gbl...

I have a table of the most commonly incorrectly answered questions in my
testing application. Imagine the one column table looks like:

a
a
a
b
b
b
b
c
c
d

(so b is the most common, d is the least common). What I want to do is
retrieve the top 3 most commonly appearing questions in this table. If I

do:

"Select Top 3 answers from incorrectAnswers"

The dataset looks like:

a
a
a

That makes no sense to me, it should be:

b
a
c

I thought that maybe the returned records are each individual record,
unordered, but if I say,

"Select Top 3 answers from incorrectAnswers Group By answers", I get:

a
b
c

Huh? Is it defaulting to ordering alphabetically? How can I order by the "top" records?

Thanks!
D

[Fergus Cooney]

Hi MCD,

Using Larry's SQL as a base, I came up with the following:

SELECT TOP 3 Incorrect.Answer, Count(Incorrect.Answer)
FROM Incorrect
GROUP BY Incorrect.Answer
ORDER BY Count(Incorrect.Answer) DESC;

Given [a a a b b b b b b c d d d] this produces {b 6} {a 3} {d 3}.

To take out the numbers just use
SELECT TOP 3 Incorrect.Answer
FROM Incorrect
GROUP BY Incorrect.Answer
ORDER BY Count(Incorrect.Answer) DESC;

Incorrect is the Table, Answer is the Column.

What it's saying is
GROUP BY Incorrect.Answer
Collect all the same answers together (ie they don't need to be in
order).

ORDER BY Count(Incorrect.Answer) DESC;
Order these groups by the number in each, starting with the largest.

TOP 3
Only show the top 3.

TOP 3 Incorrect.Answer
Only show the Answer.

Regards,
Fergus

[MC D]

Thanks Fergus, that did the trick. Also thanks for the explanation, it was
a big help in my understanding.

-D

"Fergus Cooney" <fi******@tesco.net> wrote in message
news:OY**************@TK2MSFTNGP10.phx.gbl...

Hi MCD,

Using Larry's SQL as a base, I came up with the following:

SELECT TOP 3 Incorrect.Answer, Count(Incorrect.Answer)
FROM Incorrect
GROUP BY Incorrect.Answer
ORDER BY Count(Incorrect.Answer) DESC;

Given [a a a b b b b b b c d d d] this produces {b 6} {a 3} {d 3}.

To take out the numbers just use
SELECT TOP 3 Incorrect.Answer
FROM Incorrect
GROUP BY Incorrect.Answer
ORDER BY Count(Incorrect.Answer) DESC;

Incorrect is the Table, Answer is the Column.

What it's saying is
GROUP BY Incorrect.Answer
Collect all the same answers together (ie they don't need to be in
order).

ORDER BY Count(Incorrect.Answer) DESC;
Order these groups by the number in each, starting with the largest.
TOP 3
Only show the top 3.

TOP 3 Incorrect.Answer
Only show the Answer.

Regards,
Fergus