Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    2

    Unanswered: SQL Server 7 - need first() last() equivalent

    This is making my head explode, so I thought I'd see if anyone here can help out.

    I've made several attempts to do this in a SQL statement, but all have failed. I can do it correctly in a script, but thats not what I want to use.

    I have a table with the following columns:

    id - primary key of course
    userid - numeric userid that corresponds with our users table
    eventdate - the unix timestamp of when the even occurred
    logintype - 1 for login, 2 for logout

    I want to write a query that will give me the last action of every user. So it will dump out a dataset of distinct userid's with the eventdate and logintype, essentially telling me who is logged in, if their last even was a login.

    Thanks in advance for the help.

    James
    Last edited by syntax; 07-12-04 at 02:22.

  2. #2
    Join Date
    Jul 2004
    Posts
    2

    One other thing.

    I am only wanting to return the userid of the users that are currently logged in, so...

    SELECT userid FROM UserLogin WHERE logintype = '1'

    I was able to do this in access with a snapshot of the data with.

    SELECT userid FROM UserLogin GROUP BY userid HAVING last(logintype) = 1

    But SQL SERVER 7 doesn't have support for the first() or last() functions.
    Last edited by syntax; 07-12-04 at 02:21.

  3. #3
    Join Date
    Mar 2004
    Posts
    20
    HI

    Write a query using MAX(eventdate) grouped by the user. Then join this to the original table in your final view. It should work fine.
    I'm sure clever folks could do it in one view but the effect will be the same.

    Gerry

Posting Permissions

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