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 6 2027
"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
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
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
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
"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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Kingdom |
last post by:
In my data base I have a list of componet types e.g. type A - I have 8 off
- type B I have 12 off etc.
I'm using
Set objRS = objDC.Execute("Select DISTINCT Component_Type FROM
Parts_Table")
...
|
by: Mark Reed |
last post by:
Hi all,
I am trying to learn a little about programming (I know next to nothing
so far) and have found some code which hides the toolbars. However, this bit
of code is a little too effective and...
|
by: drose0927 |
last post by:
Please help! I can't get my program to exit if the user hits the
Escape button:
When I tried exit(EXIT_SUCCESS), it wouldn't compile and gave me this
error:
Parse Error, expecting `'}''...
|
by: Jesee |
last post by:
I am reading Jeffrey Richter's book "Applied Microsoft .NET Framework
programming",i came across "Exception handing". Page 405 says
"If the stack overflow occurs within the CLR itself,your...
|
by: Scott C. Reynolds |
last post by:
In VB6 you could do a SELECT CASE that would evaluate each case for
truth and execute those statements, such as:
SELECT CASE True
case x > y:
dosomestuff()
case x = 5:
dosomestuff()
case y >...
|
by: Greg Corradini |
last post by:
Hello All,
A few weeks ago, I wrote two scripts using mx.ODBC on an Access DB. Among
other things, both scripts create new tables, perform a query and then
populate the tables with data in a...
|
by: redashley40 |
last post by:
This is my first attempt in SQL and PreparedStatement
I have add the PreparedStatement and I'm not to sure if I'm doing it correctly.
When I do a test run on Choose 1 ,or 2 I get this error.
Error...
|
by: rookiejavadude |
last post by:
I'm have most of my java script done but can not figure out how to add a few buttons. I need to add a delete and add buttong to my existing java program. Not sure were to add it on how. Can anyone...
|
by: RCapps |
last post by:
When running the below SQL Query I keep getting the following error:
Server: Msg 4924, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN failed because column 'ContractDef' does not exist in table...
|
by: kya2 |
last post by:
I am not able to create following store procedure.
CREATE PROCEDURE DBSAMBA.InsertDeleteBatch(OUT norows INT )
RESULT SETS 1
LANGUAGE SQL
BEGIN part1
DECLARE TOTAL_LEFT INT DEFAULT 0;
...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |