1. Registered User
Join Date
Aug 2006
Location
Leeds, UK
Posts
104

Hi

I have a login table which logs (creates a new row) a user anytime they log in or out (time stamp, their userId and whether they were logging in or out).

Can someone point me in the right direction to finding out how i 'select' each distinct userId and their last status, eg whether they are currently logged in or not?

for example (1 = logged in)

rowId userId status
1 222 1
2 333 1
3 222 0
4 444 1
5 222 1
6 333 0
7 333 1

So above there are 3 unique users (222, 333 and 444), i need to select the last row (ie the last time they each logged in or out) for each distinct userId, so there would be three rows returned:

rowId userId status
5 222 1
7 333 1
4 444 1

sounds simple, but can't figure it out, do i have to itterate through it some how?

2. Registered User
Join Date
May 2009
Posts
509
mattock, Assuming RowId is an incremented number for each row as it is inserted to the table, then this should work.
Code:
```SELECT RowId, UserId, Status
FROM table-name A
WHERE RowId = (SELECT MAX(RowId)
FROM table-name B
WHERE A.UserId = B.UserId
)```
PS Your mentioned a Timestamp and that would probably work better for finding the most recent row for each UserId value.

3. Registered User
Join Date
Aug 2006
Location
Leeds, UK
Posts
104
Hi

Thanks for your reply, how ever this would only return one row, i need to return one row for each UserId, so 3 rows in the above example. I'm thinking i may need to use a 'for each' loop some how? It needs to select the max(id) for each UserId somehow?

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by mattock
...this would only return one row
actually, it would return one row for each UserId

Originally Posted by mattock
It needs to select the max(id) for each UserId somehow?
that's just what it does

maybe you should test it

5. Registered User
Join Date
May 2009
Posts
509
mattock, Actually, since it is a correlated subquery, it returns one row per distinct UserId (the correlation between the main query and the subquery).

Using your example (in the order you listed although that doesn't really matter)
Row 1- RowId: 1, UserId: 222, Status: 1 and the WHERE clause will check RowId against the Max RowId from the subquery for all row where UserId = 222. Since this is a 5, the WHERE is False and Row 1 is NOT returned.

Row 2- RowId: 2, UserId: 333, Status: 1 and the WHERE clause will check RowId against the Max RowId from the subquery for all row where UserId = 333. Since this is a 7, the WHERE is False and Row 2 is NOT returned.

Row 3- RowId: 3, UserId: 222, Status: 0 and the WHERE clause will check RowId against the Max RowId from the subquery for all row where UserId = 222. Since this is a 5, the WHERE is False and Row 3 is NOT returned.

Row 4- RowId: 4, UserId: 444, Status: 1 and the WHERE clause will check RowId against the Max RowId from the subquery for all row where UserId = 444. Since this is a 4, the WHERE is True and Row 4 IS returned.

Row 5- RowId: 5, UserId: 222, Status: 1 and the WHERE clause will check RowId against the Max RowId from the subquery for all row where UserId = 222. Since this is a 5, the WHERE is True and Row 5 IS returned.

Row 6- RowId: 6, UserId: 333, Status: 1 and the WHERE clause will check RowId against the Max RowId from the subquery for all row where UserId = 333. Since this is a 7, the WHERE is False and Row 6 is NOT returned.

Row 7- RowId: 7, UserId: 333, Status: 1 and the WHERE clause will check RowId against the Max RowId from the subquery for all row where UserId = 333. Since this is a 7, the WHERE is True and Row 7 IS returned.

Rows 4, 5, and 7 will be returned (which are the rows you indicated)

PS If you have a lot of rows, there may be more efficient ways to do this. As it is, the MAX function will be processed for each row in the table along with the Sort required (if you don't have the right index)

6. Registered User
Join Date
Aug 2006
Location
Leeds, UK
Posts
104
thanks, i did test it but i only had one test row, duh!

thanks guys!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•