asked on

View with most recent record

I have a table that has historical user records. This is imported. It contains a columns as follows:

RecordId, UserId, AnotherId, LastName, FirstName, AcctType, etc...., ChangeDate
RecordIdis the primary key

I need to get the most recent record for each UserId. I need the entire record. There may be multiple records for a given UserId. In those cases the most recent record will be determined by the most recent ChangeDate.

Some records may not have a UserId (i.e. be null for that column). I do not want to include those records.

I know this is a correlated subquery, but am not so great at writing them - it would be a lot faster if someone could help.

SELECT UserTable.RecordId
	, UserTable.UserId
	, UserTable.LastName
	, UserTable.FirstName
	, UserTable.AcctType
	, ...
FROM MySchema.UserTable
WHERE UserTable.RecordId = ...

Open in new window

Om Prakash

--This is for given user id

SELECT UserTable.RecordId
      , UserTable.UserId
      , UserTable.LastName
      , UserTable.FirstName
      , UserTable.AcctType
FROM MySchema.UserTable
WHERE UserTable.UserId = 1
ORDER BY ChangeDate DESC

Om Prakash

--If you want this for all users

SELECT
       max(ChangeDate)
      , UserTable.UserId
      , UserTable.LastName
      , UserTable.FirstName
      , UserTable.AcctType
FROM MySchema.UserTable
Group by
UserTable.UserId, UserTable.LastName, UserTable.FirstName, UserTable.AcctType
ORDER BY ChangeDate DESC

Dmitriy

DECLARE @temptable TABLE (
uid char(30),
changedate datetime )

INSERT INTO @temptable (uid , changedate )
SELECT UserId, max(ChangeDate)
from UserTable
group by UserId

Then you can do the following:
SELECT u.* from UserTable u, @temptable t
where UserId = uid
and ChangeDate = changedate

ASKER CERTIFIED SOLUTION

conraba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mrichmon

ASKER

Thank you - exactly what I was looking for