Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104

    Unanswered: selecting distinct rows

    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?

    thanks in advance

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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. #3
    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. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mattock
    ...this would only return one row
    actually, it would return one row for each UserId

    Quote 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


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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. #6
    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
  •