Determine most recent 'vote type' for a record, returning 2 fields for HasVotedUp/Down?

I have a SQL Server 2008 table containing 'votes'. They are either a 'VoteUp' or 'VoteDown' VoteTypeId (1 or 2 respectively).

Each vote has a timestamp of when the vote was inserted in the table.

Each vote row belongs to a post (FK PostId).

Each vote row belongs to a user (FK UserId).

I have a query to bring back a list of posts, and in that same query, I want to identify if the current user has voted that post up or down (or not at 开发者_运维百科all).

In some cases, a single post may contain both an Up AND a Down vote, where the user changed their mind at a later date (changing a Down vote to an Up vote). In this scenario, I want to identify the latest vote as being the relevant vote (the original down vote is simply held in the DB for historic purposes).

Ideally, I'd like 2 simple BIT fields returned within the Query: HasVotedUp and HasVotedDown.

I could include something like this (cut down for brevity), to simply return whether the most recent vote for that user/post is an Up or Down vote, then assign the 'HasVotedUp' and 'HasVotedDown' properties in code based on the returned VoteTypeId...

select p.id, p.body,
(select top 1 votetypeid from votes where userid = 1 and postid = p.id order by creationdate desc) as CurrentVoteTypeId
from posts p

... but I wondered if there was a similarly simple (but importantly, efficient) approach that I could use to return the 2 'HasVotedUp/Down' fields within the query, so it's on a plate, ready to use?

This nested approach was all I came up with so far but I'm sure this is inefficient?!

select cast((select count(1)
             from   votes
             where  userid = 1
                    and postid = p.id
                    and votetypeid = 1
                    and creationdate > (select creationdate
                                        from   votes
                                        where  userid = 1
                                               and postid = p.id
                                               and votetypeid = 2)) as bit) as
       HasVotedUp,
       cast((select count(1)
             from   votes
             where  userid = 1
                    and postid = p.id
                    and votetypeid = 2
                    and creationdate > (select creationdate
                                        from   votes
                                        where  userid = 1
                                               and postid = p.id
                                               and votetypeid = 1)) as bit) as
       HasVotedDown
from   posts p  

This should be very scalable:

select p.id,
    CASE WHEN vt.votetypeid = 1 THEN 1 ELSE 0 END AS HasVotedUp,
    CASE WHEN vt.votetypeid = 2 THEN 1 ELSE 0 END AS HasVotedDown
from posts p left outer join
    (
        select v.postid, v.votetypeid
        from votes v inner join
        (
            select postid, userid, max(creationdate) as creationdate
            from votes
            group by postid, userid
        ) newest on v.postid = newest.postid and v.userid = newest.userid and v.creationdate = newest.creationdate
        where v.userid = 1
    ) vt on p.id = vt.postid

Any good to you?

SELECT p.id,
       O.*
FROM   posts p
       OUTER APPLY (SELECT MAX(CASE
                                 WHEN RN = 1 THEN votetypeid
                               END)            AS currentvotetypeid,
                           ISNULL(MAX(CASE
                                        WHEN votetypeid = 1 THEN 1
                                      END), 0) AS HasVotedUp,
                           ISNULL(MAX(CASE
                                        WHEN votetypeid = 2 THEN 1
                                      END), 0) AS HasVotedDown
                    FROM   (SELECT ROW_NUMBER() OVER (ORDER BY creationdate DESC) RN,
                                   *
                            FROM   votes
                            WHERE  userid = 1
                                   AND postid = p.id) T) O