multiple choice - (brainer, at least for me)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

multiple choice - (brainer, at least for me)

Author

Topic

Limbic
Starting Member

15 Posts

Posted - 2006-05-16 : 09:17:30

Hi guys,

I’ve got a problem which has been annoying me quite a time and need some help here.
The problem is to check if all answers for a multiple choice test given by an employee are correct.
Some times more than one answer is possible for a question.
An employee has only got the question right if he has chosen ALL correct answers.

So there are basically two tables.
The first table holds possible answers for a question ,
the column isCorrect determines whether the answer is correct or not.


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[answers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[answers]
GO

CREATE TABLE [dbo].[answers] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[questionId] [int] NULL ,
	[answerId] [int] NULL ,
	[text] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
	[isCorrect] [bit] NULL 
) ON [PRIMARY]
GO

--The second one is the log table that holds the answers chosen by an employee

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[answerLogs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[answerLogs]
GO

CREATE TABLE [dbo].[answerLogs] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[attempt] [int] NOT NULL, -- every attempt gets a unique number
	[questionId] [int] NOT NULL ,
	[answerId] [int] NOT NULL ,
	[employeeId] [int] NOT NULL 
) ON [PRIMARY]
GO


INSERT INTO answers(questionId,answerId,[text],isCorrect)
VALUES (1,1,'the answer is...',1)
INSERT INTO answers(questionId,answerId,[text],isCorrect)
VALUES (1,2,'the answer is...',1)
INSERT INTO answers(questionId,answerId,[text],isCorrect)
VALUES (1,3,'the answer is...',0)
INSERT INTO answers(questionId,answerId,[text],isCorrect)
VALUES (1,4,'the answer is...',0)
INSERT INTO answers(questionId,answerId,[text],isCorrect)
VALUES (1,5,'the answer is...',1)
INSERT INTO answers(questionId,answerId,[text],isCorrect)
VALUES (1,6,'the answer is...',1)

-- Means, an employee has to choose 4 answers (1,2,5,6) to get the question right
-- The inserts below simulate an attempt which isn’t correct because the 
-- employee chose all answers 
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(1,1,1,1007)
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(1,1,2,1007)
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(1,1,3,1007)
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(1,1,4,1007)
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(1,1,5,1007)
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(1,1,6,1007)

--The inserts below simulate an attempt which is wrong because 
--Answers 3,4 are not correct
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(2,1,1,1007)
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(2,1,2,1007)
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(2,1,3,1007)
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(2,1,4,1007)

I excluded the question table. The log table and the answer table hold only data for one question. (questionId = 1)

My first idea was count the chosen answers and check if they match with the correct answers of the answer table


SELECT questionId, COUNT(answerId) FROM  answerlogs l 
WHERE questionId = 1
and attempt = 1
GROUP BY questionId
HAVING COUNT(l.answerId) = (SELECT COUNT(answerId) FROM answers a WHERE isCorrect = 1)

But then I realized I don't check if the chosen answers are correct.
That's the point where I'm struggeling.

I literally tried hundreds of queries I joined them and tried HAVING COUNT
But it never worked. How can I check if ALL of the correct answers were chosen?

I hope someone can explain me how this works

Many thanks,

Limbic

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-16 : 09:49:03

Here is one possible way. I've added a set of correct answers to your DML for testing. It uses a single value represented by the sum of the correct answerIDs to the second power compared to the same algorithm for the student's answers.


--The inserts below simulate an attempt which is correct because 
--All Answers are correct
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(3,1,1,1007)
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(3,1,2,1007)
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(3,1,5,1007)
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(3,1,6,1007)


select	a.employeeid
	,a.questionid
	,a.attempt
	,Result = case
		when AnswerByte = AllCorrectByte then 'Correct'
		else 'Wrong'
		end
	,a.answerByte
	,q.AllCorrectByte
from	(
	select	questionid
		,AllCorrectByte = sum(power(2,answerid))
	from	answers
	where	isCorrect = 1
	group by questionid
	) q
left join
	(
	select employeeid
		,questionid
		,attempt
		,AnswerByte = sum(power(2,answerid))
	from	answerlogs
	group by employeeid
		,questionid
		,attempt
	) a
		on a.questionid = q.questionid

output:
employeeid  questionid  attempt     Result  answerByte  AllCorrectByte 
----------- ----------- ----------- ------- ----------- -------------- 
1007        1           1           Wrong   126         102
1007        1           2           Wrong   30          102
1007        1           3           Correct 102         102

EDIT:
I corrected this code.
~~power(anserid, 2)~~ power(2, anserid)

Be One with the Optimizer
TG

mr_mist
Grunnio

1870 Posts

Posted - 2006-05-16 : 09:56:41

Yeah, i was just working along the same lines, had just pondered that this would have been a whole lot easier had the answerids had an exclusive pattern to them, then you could have just added the sums of answers against correct answers.

1,2,4,8,16,32,64 etc.

-------
Moo. :)

Limbic
Starting Member

15 Posts

Posted - 2006-05-16 : 10:02:49

TG

You my friend are brilliant and fast!!!

Just to devistate my self-confidence, was that kind of question an easy question or was it more of a tricky one?

Limbic,

thanks again mate!!!!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-16 : 10:15:39

LOL,
Not so brilliant, just old. It was easy, but only because I have struggled through similar things before and now include that "trick" in my "bucket o' sulutions".

btw,
You may need to flip the derived tables in the left join so that un-answered questions get marked as wrong instead of not showing up.

Be One with the Optimizer
TG

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-16 : 10:21:19

Nice work TG.

That formula for AnswerByte worries me a little though. e.g. if the answer is 3 and 4 and I put 5, won't it say I'm correct? (since 3^2 + 4^2 = 5^2)

If so, maybe we could replace "sum(power(answerid,2))" with "CHECKSUM_AGG(BINARY_CHECKSUM(answerId))" - although even with this there is still a 'small' chance 2 different sets of values will give the same aggregated value.

I keep meaning to research the 'best aggregation method' (i.e. the one that gives the smallest probability of error, given certain constraints) for things like this (e.g. maybe you can use log or other mathematical operations before aggregating). And maybe someone out there already has (although I've not been able to find anything

). One of these days...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-16 : 10:33:27

Woops!! Thanks Ryan, I got my powers backwards (I wonder if that ever happens to superman)

should be:
sum(power(2, answerid))


select	power(2,a) [power(2,a)]
from	(select 3 a union select 4) a

select sum(power(2,a)) [sum(power(2,a))]
from	(select 3 a union select 4) a


power(2,a)  
----------- 
8
16

sum(power(2,a)) 
--------------- 
24

EDIT:
I corrected the code above

Be One with the Optimizer
TG

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-16 : 10:45:06

That's only going to give you answerids up to around 1023...

select power(cast(2 as float(53)), 1023) = 8.9884656743115795E+307
select power(cast(2 as float(53)), 1024) = overflow

Please correct me if I'm wrong.

So the checksum is still probably a better bet. But hold on a sec, I'm working on another idea...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-05-16 : 10:54:27

Here is another possibility that avoids special numbering schemes. It also can check multiple questions/employees/attempts at one go.

EDIT: Part in blue is really the main part


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[answers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[answers]
GO

CREATE TABLE [dbo].[answers] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[questionId] [int] NULL ,
	[answerId] [int] NULL ,
	[text] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
	[isCorrect] [bit] NULL 
) ON [PRIMARY]
GO

--The second one is the log table that holds the answers chosen by an employee

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[answerLogs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[answerLogs]
GO

CREATE TABLE [dbo].[answerLogs] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[attempt] [int] NOT NULL, -- every attempt gets a unique number
	[questionId] [int] NOT NULL ,
	[answerId] [int] NOT NULL ,
	[employeeId] [int] NOT NULL 
) ON [PRIMARY]
GO


INSERT INTO answers(questionId,answerId,[text],isCorrect)
VALUES (1,1,'the answer is...',1)
INSERT INTO answers(questionId,answerId,[text],isCorrect)
VALUES (1,2,'the answer is...',1)
INSERT INTO answers(questionId,answerId,[text],isCorrect)
VALUES (1,3,'the answer is...',0)
INSERT INTO answers(questionId,answerId,[text],isCorrect)
VALUES (1,4,'the answer is...',0)
INSERT INTO answers(questionId,answerId,[text],isCorrect)
VALUES (1,5,'the answer is...',1)
INSERT INTO answers(questionId,answerId,[text],isCorrect)
VALUES (1,6,'the answer is...',1)

-- Means, an employee has to choose 4 answers (1,2,5,6) to get the question right
-- The inserts below simulate an attempt which is correct because the 
-- employee chose all answers 
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(1,1,1,1007)
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(1,1,2,1007)
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(1,1,5,1007)
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(1,1,6,1007)


-- The inserts below simulate an attempt which isn’t correct because the 
-- employee chose all answers 
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(2,1,1,1007)
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(2,1,2,1007)
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(2,1,3,1007)
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(2,1,4,1007)
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(2,1,5,1007)
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(2,1,6,1007)

--The inserts below simulate an attempt which is wrong because 
--Answers 3,4 are not correct
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(3,1,1,1007)
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(3,1,2,1007)
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(3,1,3,1007)
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(3,1,4,1007)

--The inserts below simulate an attempt which is wrong because 
--Answers 3 is not correct and 6 is correct
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(4,1,1,1007)
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(4,1,2,1007)
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(4,1,3,1007)
INSERT INTO answerlogs(attempt, questionId, answerId, employeeId)
VALUES(4,1,5,1007)


--Full join shows how to get a full comparison of all answers
Select *
From 
	(
	Select * 
	From answers Z
	Cross Join (Select distinct attempt, employeeId From answerLogs) Y
	Where isCorrect = 1
	) A
Full Join answerLogs B
On A.Attempt = B.Attempt
and A.EmployeeId = B.EmployeeId
and A.questionId = B.questionId
and A.answerId = B.answerId

--Grading query.  Correct column counts the number of answers they got correct, incorrect counts the number incorrect 
Select 
	Attempt = isnull(A.Attempt,B.Attempt),
	EmployeeId = isnull(A.employeeId,B.EmployeeId), 
	QuestionId = isnull(A.QuestionId,B.QuestionId),
	Correct = sum(case when A.answerId is not null and B.AnswerId is not null then 1 else 0 end),
	Incorrect = sum(case when A.answerId is null then 1 else 0 end + case when B.answerId is null then 1 else 0 end)
From 
	(
	Select * 
	From answers Z
	Cross Join (Select distinct attempt, employeeId From answerLogs) Y
	Where isCorrect = 1
	) A
Full Join answerLogs B
On A.Attempt = B.Attempt
and A.EmployeeId = B.EmployeeId
and A.questionId = B.questionId
and A.answerId = B.answerId
Group By isnull(A.Attempt,B.Attempt), isnull(A.employeeId,B.EmployeeId), isnull(A.QuestionId,B.QuestionId)

--Correct answers can be identified by:
Select 
	Attempt = isnull(A.Attempt,B.Attempt),
	EmployeeId = isnull(A.employeeId,B.EmployeeId), 
	QuestionId = isnull(A.QuestionId,B.QuestionId),
	sum(case when A.answerId is not null and B.AnswerId is not null then 1 else 0 end),
	sum(case when A.answerId is null then 1 else 0 end + case when B.answerId is null then 1 else 0 end)
From 
	(
	Select * 
	From answers Z
	Cross Join (Select distinct attempt, employeeId From answerLogs) Y
	Where isCorrect = 1
	) A
Full Join answerLogs B
On A.Attempt = B.Attempt
and A.EmployeeId = B.EmployeeId
and A.questionId = B.questionId
and A.answerId = B.answerId
Group By isnull(A.Attempt,B.Attempt), isnull(A.employeeId,B.EmployeeId), isnull(A.QuestionId,B.QuestionId)
Having sum(case when A.answerId is null then 1 else 0 end + case when B.answerId is null then 1 else 0 end) = 0

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-16 : 10:58:32

Okay, the other idea is based on Limbic's first thought - that is, based on counting.

I thought that if we could count...

1. the correct answers
2. the attempted answers
3. the union of the 2 (this is the 'clever' bit)

...and they're all the same, then that attempt must be right

Something like this (for the example we've been working with)...

--data
declare @answers table (id int IDENTITY(1, 1), questionId int, answerId int, text nvarchar(50), isCorrect bit)
declare @answerLogs TABLE (id int IDENTITY(1, 1), attempt int, questionId int, answerId int, employeeId int)

INSERT INTO @answers(questionId,answerId,[text],isCorrect) VALUES (1,1,'the answer is...',1)
INSERT INTO @answers(questionId,answerId,[text],isCorrect) VALUES (1,2,'the answer is...',1)
INSERT INTO @answers(questionId,answerId,[text],isCorrect) VALUES (1,3,'the answer is...',0)
INSERT INTO @answers(questionId,answerId,[text],isCorrect) VALUES (1,4,'the answer is...',0)
INSERT INTO @answers(questionId,answerId,[text],isCorrect) VALUES (1,5,'the answer is...',1)
INSERT INTO @answers(questionId,answerId,[text],isCorrect) VALUES (1,6,'the answer is...',1)

INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(1,1,1,1007)
INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(1,1,2,1007)
INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(1,1,3,1007)
INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(1,1,4,1007)
INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(1,1,5,1007)
INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(1,1,6,1007)

INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(2,1,1,1007)
INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(2,1,2,1007)
INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(2,1,3,1007)
INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(2,1,4,1007)

INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(3,1,1,1007)
INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(3,1,2,1007)
INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(3,1,5,1007)
INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(3,1,6,1007)

--calculation
select *,
	Result = case
		when CountOfAnswer = CountOfAnswerLog and CountOfAnswer = CountOfUnion then 'Correct'
		else 'Wrong'
		end
from (
    select
        attempt,
        questionId,
        (SELECT COUNT(answerId) FROM @answers a where questionId = a.questionId and isCorrect = 1) as CountOfAnswer,
        (SELECT COUNT(answerId) FROM @answerlogs where attempt = a.attempt and questionId = a.questionId) as CountOfAnswerLog,
        count(*) as CountOfUnion
    from (
        select attempt, questionId, answerId from @answerlogs
        union
        select attempt, a.questionId, answerId from @answers a inner join (select distinct attempt, questionId from @answerlogs) l on a.questionId = l.questionId where isCorrect = 1
        ) a
    group by attempt, questionId
    ) b

EmployeeId needs considering, and the other data you need for the results needs adding, but I think this is quite a useful technique. The next question will be performance - and that's where you come in Limbic!

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Limbic
Starting Member

15 Posts

Posted - 2006-05-16 : 11:05:05

Thanks a lot RyanRandall and Seventhnight, puh,
I've learnt definetly some new tricks here.
Now I have to figure out which suits my db sheme the most regarding the performance.

Cheers

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-16 : 11:38:18

Okay, I just can't let this one go

What fun!

Inspired by Seventhnight's answer (nice work Seventhnight

- the best approach so far, I reckon), I've tried to consolidate and simplify things...

--data
declare @answers table (id int IDENTITY(1, 1), questionId int, answerId int, text nvarchar(50), isCorrect bit)
declare @answerLogs TABLE (id int IDENTITY(1, 1), attempt int, questionId int, answerId int, employeeId int)

INSERT INTO @answers(questionId,answerId,[text],isCorrect) VALUES (1,1,'the answer is...',1)
INSERT INTO @answers(questionId,answerId,[text],isCorrect) VALUES (1,2,'the answer is...',1)
INSERT INTO @answers(questionId,answerId,[text],isCorrect) VALUES (1,3,'the answer is...',0)
INSERT INTO @answers(questionId,answerId,[text],isCorrect) VALUES (1,4,'the answer is...',0)
INSERT INTO @answers(questionId,answerId,[text],isCorrect) VALUES (1,5,'the answer is...',1)
INSERT INTO @answers(questionId,answerId,[text],isCorrect) VALUES (1,6,'the answer is...',1)

INSERT INTO @answers(questionId,answerId,[text],isCorrect) VALUES (2,7,'the answer is...',0)
INSERT INTO @answers(questionId,answerId,[text],isCorrect) VALUES (2,8,'the answer is...',1)

INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(1,1,1,1007)
INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(1,1,2,1007)
INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(1,1,3,1007)
INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(1,1,4,1007)
INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(1,1,5,1007)
INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(1,1,6,1007)

INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(2,1,1,1007)
INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(2,1,2,1007)
INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(2,1,3,1007)
INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(2,1,4,1007)

INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(3,1,1,1007)
INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(3,1,2,1007)
INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(3,1,5,1007)
INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(3,1,6,1007)

INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(1,1,1,1008)

INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(1,2,7,1008)

INSERT INTO @answerlogs(attempt, questionId, answerId, employeeId) VALUES(2,2,8,1008)

--calculation
select *,
    Incorrect = Tries - Correct,
    Result = case
        when Answers = Tries and Answers = Correct then 'Correct'
        else 'Wrong'
        end
from (
    select
        l.employeeid,
        l.attempt,
        a.questionId,
        count(distinct a.answerId) as Answers,
        count(distinct l.answerId) as Tries,
        sum(case when a.answerId = l.answerId then 1 else 0 end) as Correct
    from @answers a inner join @answerlogs l on l.questionId = a.questionId
    where isCorrect = 1
    group by l.employeeid, l.attempt, a.questionId
    ) b

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-16 : 13:02:45

quote:
Originally posted by RyanRandall

That's only going to give you answerids up to around 1023...

select power(cast(2 as float(53)), 1023) = 8.9884656743115795E+307
select power(cast(2 as float(53)), 1024) = overflow

Yeah, I was assuming based on the sample data that answerid was reset to start at 1 for each question.
So I figured 1023 would be way more than the number of answer choices for any given question

This is a good problem though, I like all the solutions!

Be One with the Optimizer
TG

Limbic
Starting Member

15 Posts

Posted - 2006-05-16 : 13:22:08

Yep,
answerId is reset to 1 for each question therefore it 1024 - 1 is sufficient.
As you say, all solutions are great but I go for Ryans because its the easiest for me to understand when I get back to the query after some time.

Limbic

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-05-16 : 13:23:13

Ryan -

the only problem I have with your latest solution, is that inner join forces some fuzziness into the problem. This causes you to use 'distinct' on your aggregates, which I personally find confusing (and sometimes 'unsafe').


select *,
    Incorrect = Tries - Correct,
    Result = case
        when Answers = Tries and Answers = Correct then 'Correct'
        else 'Wrong'
        end
from (
    select
        l.employeeid,
        l.attempt,
        a.questionId,
        count(distinct a.answerId) as Answers,
        count(distinct l.answerId) as Tries,
        sum(case when a.answerId = l.answerId then 1 else 0 end) as Correct
    from @answers a inner join @answerlogs l on l.questionId = a.questionId
    where isCorrect = 1
    group by l.employeeid, l.attempt, a.questionId
    ) b

It might make better to use a left join instead... Although, I believe I still prefer the Full Join in my original


	Select 
	        l.employeeid,
	        l.attempt,
	        a.questionId,
		C.Answers,
		Tries = count(*),
		Correct = sum(case when A.isCorrect = 1 then 1 else 0 end)
	From @answerlogs L
	Inner Join (Select questionId, Answers = count(*) From @answers A Where isCorrect=1 Group By questionId) C
	On L.questionId = C.questionId
	Left Join @answers A
	On L.questionId = A.questionId
	and L.answerId = A.answerId
	group by l.employeeid, l.attempt, a.questionId, C.Answers
	) b

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-16 : 13:34:47

You might be right Seventhnight, and I'm eager to learn

So...

quote:
inner join forces some fuzziness into the problem

I don't understand this - could you explain?

quote:
(and sometimes 'unsafe')

I don't understand this either - could you explain?

Better yet, give me some examples (I find it hard to understand anything without examples!)

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-05-16 : 13:48:50

For some one following behind in your code, a distinct can hide the results of some messy joins. As for an example, look at your join without the group by...


    select
        l.employeeid,
        l.attempt,
        a.questionId,
	*
--        count(distinct a.answerId) as Answers,
--        count(distinct l.answerId) as Tries,
--        sum(case when a.answerId = l.answerId then 1 else 0 end) as Correct
    from @answers a inner join @answerlogs l on l.questionId = a.questionId
    where isCorrect = 1
--    group by l.employeeid, l.attempt, a.questionId
    Order By L.EmployeeId, L.attempt, L.questionId

for the first set check (EmployeeId:1007;attempt:1;questionId:1;) the join returns 24 records... the result of 6 possible answers multiplied by the 4 actual correct answers. In the worst case, you shouldn't have to deal with more records than the total possible responses (6). My original query avoids this confusion, by including the answerId into the join (which you can't do because the inner join would start dropping records).

Oh and unsafe to use distinct sometimes, as you can unintentionally loose/hide issues (such as duplicate records...)

A full join is not efficient or the 'right' choice often, but I find that it fits nicely for comparison of sets. It allows you to see:
- what is in A but not in B
- what is in B but not in A
- And what is in A and in B

Sums of those three scenarios give you answers, correct, and incorrect

Hope that helps!

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-05-16 : 16:10:23

I think there are two types of data sets:
1. the number of possible correct answer for a questions
2. Then number of CORRECT ANSWER by the user to a question.

With number 2, I think you should include that the ANSWERS are correct, not just the answers. That is, FROM (SELECT.. FROM answerlogs WHERE IsCorrect = 1) LEFT JOIN (select .. FROM answers JOIN answerlogs WHERE IsCorrect = 1).

May the Almighty God bless us all!

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-17 : 06:11:46

quote:
For some one following behind in your code, a distinct can hide the results of some messy joins.

Seventhnight - Thanks for taking the time to explain your thinking

However, I think I'm going to have to disagree

All I am seeing in your explanation is that things can be bad if the data is bad or if the coder is bad. I don't see why either of those things should influence the design.

If the query you've suggested performs better, then that's another story.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-17 : 07:55:16

Generally speaking, with no regards to the solutions above, I have to agree with what Corey said about the Distinct vs Group By. Though most often the plan is the same, and person doing the coding usually knows what they're doing (especially if it's Ryan

), there will be no difference. But I think of using a DISTINCT as just throwing a "blanket of unique-ness" on whatever the results are as opposed to GROUPing by an explicit column set. So just to remove any ambiguity for others as well as myself when I revisit old code, I like to use GROUP By rather than DISTINCT when possible.

As a related peeve, I hate when developers who are faced with dupes in there results just slap a distinct at the top rather than figuring out why the dupes are there. (especially when there are not just 2 identical rows but hundreds!)

Be One with the Optimizer
TG

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-17 : 08:21:55

I don't think the DISTINCT keyword should be the victim. I've see developers get rid of duplicates by throwing GROUP BY all over the place!

I think the general point (or peeve

) is that it's rarely good practice to do anything if you don't understand it. And particularly with SQL

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources