Geek asks and answers

Choosing the most effective answers

SQL Fiddle: http://sqlfiddle.com/#!3/9b459/6

I have a table that answers the question, “Will you take part in this event?” Each user can answer several times, and all answers are stored in a table. Usually we are only interested in the last answer, and I try to build an effective query for it. I am using SQL Server 2008 R2.

Table contents for one event:

Table contents

Column types: int, int, datetime, bit
Primary key: (EventId, MemberId, Timestamp)

Note that Participant 18 first answered No and later Yes, participant 20 answered Yes first and later No, participant 11 answered No and later No again. I would like to filter these answers first. In addition, there can be more than one answer that needs to be filtered out - the user can, for example, answer Yes, Yes, No, Yes, No, No, No.

SQL Server Management Studio, , Display Estimated Execution Plan . ?

, :

-----------------------------------------------------------------
-- Subquery to select Answer (does not include Timestamp)
-- Cost: 63 %
-----------------------------------------------------------------
select distinct a.EventId, a.MemberId,
(
  select top 1 Answer
  from    Attendees
  where EventId   = a.EventId
  and   MemberId  = a.MemberId
  order by Timestamp desc
) as Answer
from    Attendees a
where a.EventId = 68

-----------------------------------------------------------------
-- Where with subquery to find max(Timestamp)
-- Cost: 13 %
-----------------------------------------------------------------
select a.EventId, a.MemberId, a.Timestamp, a.Answer
from     Attendees a
where  a.EventId = 68
and    a.Timestamp =
(
  select max(Timestamp)
  from     Attendees
  where  EventId  = a.EventId
  and    MemberId = a.MemberId
)
order by a.TimeStamp;

-----------------------------------------------------------------
-- Group by to find max(Timestamp)
-- Subquery to select Answer matching max(Timestamp)
-- Cost: 23 %
-----------------------------------------------------------------
select a.EventId, a.MemberId, max(a.Timestamp),
(
  select top 1 Answer
  from    Attendees
  where EventId   = a.EventId
  and   MemberId  = a.MemberId
  and   Timestamp = max(a.Timestamp)
) as Answer
from    Attendees a
where a.EventId = 68
group by a.EventId, a.MemberId
order by max(a.TimeStamp);

. group by, "". - , , , SQL:

select a.EventId, a.MemberId, max(a.Timestamp), a.Answer <-- Picked from the line selected by max(a.Timestamp)
from  Attendees a
where a.EventId = 68
group by a.EventId, a.MemberId
order by max(a.TimeStamp);

?

EDIT:

SQL Fiddle, : http://sqlfiddle.com/#!3/9b459/6

+5
3

CTE, , :

SELECT T.EventId, T.MemberId, T.TimeStamp, T.Answer
FROM TableName T
 JOIN (
   SELECT EventId, MemberId, Max(Timestamp) MaxTimeStamp
   FROM TableName
   GROUP BY EventId, MemberId ) T2 ON T.EventId = T2.EventId 
    AND T.MemberId = T2.MemberId 
    AND T.TimeStamp = T2.MaxTimeStamp

, CTE .

- - SQL Fiddle - .

.

+3

SQL Server 2008 .

WITH recordsList
AS
(
    SELECT  EventID, MemberID, TimeStamp, Answer,
            ROW_NUMBER() OVER (PARTITION BY EventID, MemberID
                                ORDER BY Timestamp DESC) rn
    FROM    tableName
)
SELECT  EventID, MemberID, TimeStamp, Answer
FROM    recordsList
WHERE   rn = 1
+7

Another option

SELECT a.EventId, a.MemberId, a.Timestamp, a.Answer
FROM Attendees a
WHERE a.EventId = 68 AND EXISTS (
              SELECT 1
              FROM Attendees
              WHERE EventId = a.EventId             
              GROUP BY MemberId
              HAVING  MAX(Timestamp) = a.Timestamp                      
                      AND MemberId  = a.MemberId
              )

Demo on SQLFiddle

+3
source

More articles:


All Articles