Count Attempts!

Hi all,
I want to SELECT the most recent ATTEMPT_ID (in this case attempt_id=3)
and determine how many attempts (using COUNT) have IDENTICAL QUESTION LISTS
based on the ATTEMPT_RESULTS table?
IE:
Attempt 2 has an IDENTICAL QUESTION LIST (to 3) in the ATTEMPT_RESULTS
table using the DDL below.
Attempt 1 does not qaulify because it has an extra question 'id = 4'.
Based on the data below the result of this query should be 2.
I would appreciate any help in this, as i am not sure how to implement this
logic in SQL.
Thanks to those who responsd.
DDL:
CREATE TABLE [dbo].[attempts](
[attempt_id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_attempts] PRIMARY KEY CLUSTERED
([attempt_id] ASC) ON [PRIMARY]) ON [PRIMARY]
CREATE TABLE [dbo].[attempt_results](
[attempt_result_id] [int] IDENTITY(1,1) NOT NULL,
[attempt_id] [int] NULL,
[question_id] [int] NOT NULL,
CONSTRAINT [PK_attempt_results] PRIMARY KEY CLUSTERED
([attempt_result_id] ASC) ON [PRIMARY]) ON [PRIMARY]
INSERT INTO [attempts] ([name]) VALUES ('Temp 1')
INSERT INTO [attempts] ([name]) VALUES ('Temp 2')
INSERT INTO [attempts] ([name]) VALUES ('Temp 3')
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,1)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,2)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,3)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,4)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,1)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,2)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,3)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,1)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,2)
INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,3)Hi Adam
Thanks for the DDL and example data. Maybe something like the following is
what you require:
SELECT r.[attempt_id]
FROM [dbo].[attempt_results] r
JOIN [dbo].[attempt_results] a ON a.[attempt_id] = 3
AND a.[attempt_id] <> r.[attempt_id]
AND a.[question_id] = r.[question_id]
GROUP BY r.[attempt_id]
HAVING count(*) = ( SELECT COUNT(question_id) as cnt
FROM [dbo].[attempt_results]
WHERE [attempt_id] = 3 )
John
"Adam Knight" wrote:

> Hi all,
> I want to SELECT the most recent ATTEMPT_ID (in this case attempt_id=3)
> and determine how many attempts (using COUNT) have IDENTICAL QUESTION LIST
S
> based on the ATTEMPT_RESULTS table?
> IE:
> Attempt 2 has an IDENTICAL QUESTION LIST (to 3) in the ATTEMPT_RESULTS
> table using the DDL below.
> Attempt 1 does not qaulify because it has an extra question 'id = 4'.
> Based on the data below the result of this query should be 2.
> I would appreciate any help in this, as i am not sure how to implement thi
s
> logic in SQL.
> Thanks to those who responsd.
> DDL:
> CREATE TABLE [dbo].[attempts](
> [attempt_id] [int] IDENTITY(1,1) NOT NULL,
> [name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> CONSTRAINT [PK_attempts] PRIMARY KEY CLUSTERED
> ([attempt_id] ASC) ON [PRIMARY]) ON [PRIMARY]
>
> CREATE TABLE [dbo].[attempt_results](
> [attempt_result_id] [int] IDENTITY(1,1) NOT NULL,
> [attempt_id] [int] NULL,
> [question_id] [int] NOT NULL,
> CONSTRAINT [PK_attempt_results] PRIMARY KEY CLUSTERED
> ([attempt_result_id] ASC) ON [PRIMARY]) ON [PRIMARY]
> INSERT INTO [attempts] ([name]) VALUES ('Temp 1')
> INSERT INTO [attempts] ([name]) VALUES ('Temp 2')
> INSERT INTO [attempts] ([name]) VALUES ('Temp 3')
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,1)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,2)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,3)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,4)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,1)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,2)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,3)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,1)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,2)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,3)
>
>|||this should do:
e.g.
create function dbo.getlist(@.attempt_id int)
returns sysname
as
begin
declare @.s sysname
select @.s=isnull(@.s+'|','')+cast(question_id as sysname)
from attempt_results
where attempt_id=@.attempt_id
order by question_id
return @.s
end
go
select x.id,count(*) cnt
from(select max(attempt_id) id
from attempt_results) x join (select distinct attempt_id id
from attempt_results) y
on dbo.getlist(x.id)=dbo.getlist(y.id)
group by x.id
-oj
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:%23WIiFcYxFHA.700@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I want to SELECT the most recent ATTEMPT_ID (in this case attempt_id=3)
> and determine how many attempts (using COUNT) have IDENTICAL QUESTION
> LISTS based on the ATTEMPT_RESULTS table?
> IE:
> Attempt 2 has an IDENTICAL QUESTION LIST (to 3) in the ATTEMPT_RESULTS
> table using the DDL below.
> Attempt 1 does not qaulify because it has an extra question 'id = 4'.
> Based on the data below the result of this query should be 2.
> I would appreciate any help in this, as i am not sure how to implement
> this logic in SQL.
> Thanks to those who responsd.
> DDL:
> CREATE TABLE [dbo].[attempts](
> [attempt_id] [int] IDENTITY(1,1) NOT NULL,
> [name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> CONSTRAINT [PK_attempts] PRIMARY KEY CLUSTERED
> ([attempt_id] ASC) ON [PRIMARY]) ON [PRIMARY]
>
> CREATE TABLE [dbo].[attempt_results](
> [attempt_result_id] [int] IDENTITY(1,1) NOT NULL,
> [attempt_id] [int] NULL,
> [question_id] [int] NOT NULL,
> CONSTRAINT [PK_attempt_results] PRIMARY KEY CLUSTERED
> ([attempt_result_id] ASC) ON [PRIMARY]) ON [PRIMARY]
> INSERT INTO [attempts] ([name]) VALUES ('Temp 1')
> INSERT INTO [attempts] ([name]) VALUES ('Temp 2')
> INSERT INTO [attempts] ([name]) VALUES ('Temp 3')
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,1)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,2)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,3)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (1,4)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,1)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,2)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (2,3)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,1)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,2)
> INSERT INTO [attempt_results] (attempt_id, question_id) VALUES (3,3)
>
>sql