Help with sql Statement

  • Thread starter Thread starter Armin Zingler
  • Start date Start date
A

Armin Zingler

MC D said:
I have a table of the most commonly incorrectly answered questions in
my testing application. Imagine the one column table looks like:
Click to expand...

This is a VB.NET language group. Please turn to a group dealing with SQL
questions, e.g. microsoft.public.dotnet.framework.adonet
 
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
 
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
 
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!
 
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
 
Thanks Fergus, that did the trick. Also thanks for the explanation, it was
a big help in my understanding.

-D
 
Back
Top