Need some help with a simple SQL query.

Question

Hi folks,

I've got a game which dumps state information to a database. Eg. Connected, Lost Connection, Grabbed Screen Dump, etc.

Each state is defined by a field called StateTypeId, which is a foreign key to a simple lookup table 'StateTypes'.

Now, i'm trying to find all the people who are currently 'Connected' (eg. StateTypeId == 1), and i'm not sure how to do it. When i say currently connected, I mean that a user doesn't have a 'LostConnection' state (ie. StateType == 2) as their most recent entry.

I was thinking along the lines of.

SELECT UserId
FROM UserData
WHERE StateType = 1
GROUP BY UserId

or

SELECT UserId
FROM UserData
WHERE StateType != 1
GROUP BY UserId

but this returns all the results, not just the most recent stuff.

Any suggestions?

(i'm guessing I'm missing a TOP(1) and some rank/order by stuff?)

EDIT

DOH! i forgot to mention, i DO have a DateTime column in the table -> DateEntered.

EDIT 2 - Clarification of question AND some sample data.

I've tried the suggested solutions and i'm not having much luck. As such, i'll provide some sample data to see if this helps clarify my question.

ID State          UserName DateAdded
1  Connected      Foo      1/1/2000 12:00 
2  Connected      Bar      1/1/2000 12:01  
3  ScreenShot     Foo      1/1/2000 12:05 
4  LostConnection Foo      1/1/2000 12:06
5  Connected      Joe      1/1/2000 12:10
6  LostConnection Joe      1/1/2000 12:15
7  Screenshot     Bar      1/1/2000 12:16
8  Connected      Jane     1/1/2000 12:22

So looking at this, the people who are connected (currently) are Bar and Jane. Foo and Joe are gone. Also note, Bar's last activity a screenshot, meaning, his last activity was not a LostConnection state.

HTH.

Answer 1

What you are looking for is the "most recent" entry having a StateType of 1 or 2:

Then you are looking to identify the "last" one for each user:

select UserName, max(DateAdded) as LastDate
from UserData
where State IN ('Connected', 'LostConnection')
Group by UserName

Then you need to check the state of these (which requires a join with the original table):

SELECT UserName
FROM UserData allData join 
    (select UserName, max(DateAdded) as LastDate
    from UserData
    where State IN ('Connected', 'LostConnection')
    Group by UserName) as lastData on allData.UserName = lastData.UserName
        and allData.DateAdded = lastData.LastDate
WHERE StateType = 'Connected'

Another way to join would use the id of the UserData like this:

SELECT UserName
FROM UserData allData join 
    (select max(id) as id -- if ids are assigned in ascending order
    from UserData
    where State IN ('Connected', 'LostConnection')
    Group by UserName) as lastData on allData.id = lastData.id
WHERE StateType = 'Connected'

Answer 2

You will need a timestamp column and you'll need to write a query that joins the table to itself like this

Select *
From Userdata U
Where 1 = (Select Top 1 StateType
           From Userdata U2
           Where U.UserId = U2.UserId
           order by u2.TimeStamp desc)

Answer 3

if you have a datetime entry or time stamp you can do it this way:

Select UserID
From UserData
Where StateType = 1
Group by UserID
Having Date = Max(Date)

Answer 4

I think it would be worth testing the performance of a NOT EXISTS approach.

i.e. find users with the appropriate StateType which do not have a more recent record.

SELECT UD1.UserId
FROM  UserData AS UD1
WHERE UD1.StateType = 1
      AND NOT EXISTS
      (
--        Check for newer records:
          SELECT *
          FROM UserData AS UD2
          WHERE UD2.DateEntered > UD1.DateEntered
      )

If their are many rows stored in UserData, and/or you keep them for long periods of time, then this approach will probably perform badly.

If that is the case, and if this is a frequently occurring query, I would create a UserCurrentState table (columns = UserId, StateType) which is updated when StateType changes (I would put a Trigger on the UserData table to enforce this)

StateType in UserData is not going to index well enough to be useful. You could have an index with DateEntered, StateType, UserId which would cover the query and should therefore be used, but that will only be helpful, in practice, if you can restrict the range of DateEntered that is checked. If you are only looking for the last hour / day fine, if you are looking for since-the-beginning-of-time then it won't help much.

Answer 5

Actually, this may be more efficient (SQL Server 2005 onwards)

SELECT UserId
FROM
(
    SELECT *, row_number() 
     OVER
     (
      partition BY UserId 
      ORDER BY DateEntered DESC
     ) AS RecordNumber
    FROM UserData
) AS UD
WHERE      RecordNumber = 1
     AND StateType = 1

Answer 6

Here's another possible solution, though the solutions using NOT EXISTS probably perform better.

SELECT U1.*
FROM UserData U1
 LEFT OUTER JOIN UserData U2
 ON (U1.UserName = U2.UserName
  AND U1.DateAdded < U2.DateAdded
  AND U2.StateType IN (1,2))
WHERE U1.StateType = 1
  AND U2.UserName IS NULL
ORDER BY U1.DateAdded DESC;