I have a table that includes a primary key, a timestamp, a username, and an
IP address. I use it to log access to a user area of a website, adding a
new entry every time someone logs in. This is not a problem.

Now I want to be able to tell people who are returning after logging in
before when they last signed in and from where, but I can't seem to get the
query to work.

I keep getting errors when I try to add other fields if they aren't
specified later in a 'group by' - but when I use the 'group by' I am not
getting a single unique value for the Max timestamp value.

This is what I was trying thus far, but it's returning multiple values and I
just want the last one...

/* @userName is passed in to this query as part of a stored procedure */
SELECT
MAX(mA.[Timestamp]) AS lastLog,
mA.Remote_computer
FROM memberAccess mA
INNER JOIN members m
ON mA.memberID = m.ID
WHERE (m.userName = @userName)
GROUP BY
mA.Remote_computer

hi Catherine ,

Can you post some sample records /expected result set. The query you have written will give
you MAX(timestamp) value for each "Remote_computer" value that is belonging to a particular
username.

--
Vishal Parkar
vgparkar (AT) yahoo (DOT) co.in

Possibly this is what you want:

SELECT L.lastlog, A.remote_computer
FROM MemberAccess AS A
JOIN
(SELECT
MAX(A.[timestamp]) AS lastlog, A.memberid
FROM MemberAccess AS A
JOIN Members AS M
ON A.memberid = M.id
WHERE M.username = @username
GROUP BY A.memberid) AS L
ON A.[timestamp] = L.lastlog
AND A.memberid = L.memberid

Or, if you prefer to use TOP:

SELECT TOP 1
A.[timestamp] AS lastlog, A.remote_computer
FROM MemberAccess AS A
JOIN Members AS M
ON A.memberid = M.id
WHERE M.username = @username
ORDER BY lastlog, A.remote_computer DESC

--
David Portas
SQL Server MVP
--

Oops:
...
ORDER BY lastlog DESC, A.remote_computer

--
David Portas
SQL Server MVP
--

BTW Timestamp is a reserved word. Don't use it for a column name unless the
column name is in fact a TIMESTAMP (ROWVERSION) data type. I guess in this
case that it isn't Timestamp data (which has nothing to do with date and
time data) since you actually want to return the timestamp result.

--
David Portas
SQL Server MVP
--

Yes, I figured this much out, thus the [] around it for the time being -
thanks for the responses. I didn't post the data because the data is not
complex or really relevant for that matter. The given examples in the
second response will more than solve my problem. I was thinking something
along those lines, but just didn't know the proper syntax.

KL

"David Portas" <REMOVE_BEFORE_REPLYING_dportas (AT) acm (DOT) org> wrote

By the way - which of these would have the least overhead? I would imagine
it would be the latter as there is only one lookup going on, but I am also
aware that some sorts will take up more cpu time.

Also, what are good references with intuitive language for someone new to
SQL to learn those types of embedded select statements an the associated
syntax as demonstrated in the first example? My brain is not quite yet
getting a full grasp around the logic going on there. If it's any help, I
tend to prefer 'dry' reference material over over-fluffed step-by-step type
books.

KL

"David Portas" <REMOVE_BEFORE_REPLYING_dportas (AT) acm (DOT) org> wrote