How to find the latest record for each user? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to find the latest record for each user?

Author

Topic

neal.pressley
Starting Member

6 Posts

Posted - 2008-07-28 : 13:05:08

1. let us create a table like this
CREATE TABLE USERS_ADD_DELETE_LOG (
CLIENT_ID varchar(12) NOT NULL,
USER_ID varchar(12) NOT NULL,
ACTION Char(1) NOT NULL,
ACTION_DATE datetime ,
ACTION_BY_CLIENT_ID varchar(12) ,
ACTION_BY_USER_ID varchar(12)
)

2. Let us create data like this

insert into users_add_delete_log (client_id, user_id, Action, Action_Date, Action_By_Client_ID, Action_By_User_ID)
values ('BOBSFISH','INSURED1','A', '02/21/2008','MGA','MGAUW')

insert into users_add_delete_log (client_id, user_id, Action, Action_Date, Action_By_Client_ID, Action_By_User_ID)
values ('BOBSFISH','INSURED1','D', '03/21/2008','MGA','MGAUW2')

insert into users_add_delete_log (client_id, user_id, Action, Action_Date, Action_By_Client_ID, Action_By_User_ID)
values ('BOBSFISH','INSURED1','A', '04/21/2008','MGA','MGAFQ')

3. Action A stands for Add and D for Delete,

4. Now, i want to query the table and get all the records which has distinct combination of client_id and user_id and the most recent action is 'A'. ( i am trying a scenario when the same user record was added, deleted and then added again.)

5. I write a query like this
select DISTINCT CLIENT_ID, USER_ID , max(action_date) from users_add_delete_log WHERE CLIENT_ID LIKE 'BOB%' and action in ('A','D')
GROUP BY CLIENT_ID, USER_ID

This gives me that latest recod is of 4/21. but how shall I proceed to check if the latest record has Action A and if so, get all from the table, I want like select * .

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-28 : 13:17:18

Here's one way: (assuming you have sql server 2005)


select l.client_id
       ,l.user_id 
       ,ca.ACTION
       ,ca.ACTION_DATE
       ,ca.ACTION_BY_CLIENT_ID
       ,ca.ACTION_BY_USER_ID
from   users_add_delete_log l
cross apply (select top 1 * 
              from users_add_delete_log 
              where client_id = l.client_id 
              and user_id = l.user_id 
              order by action_date desc) ca
where ca.action = 'A'
group by l.client_id
       ,l.user_id
       ,ca.ACTION
       ,ca.ACTION_DATE
       ,ca.ACTION_BY_CLIENT_ID
       ,ca.ACTION_BY_USER_ID

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-28 : 13:26:51

[code]SELECT l.*
FROM USERS_ADD_DELETE_LOG l
INNER JOIN
(SELECT CLIENT_ID,USER_ID,MAX(ACTION_DATE) AS MaxDate
FROM USERS_ADD_DELETE_LOG
GROUP BY CLIENT_ID,USER_ID
HAVING MAX(CASE WHEN Action='A' THEN ACTION_DATE ELSE NULL END)>MAX(CASE WHEN Action='D' THEN ACTION_DATE ELSE NULL END)tmp
ON tmp.CLIENT_ID= l.CLIENT_ID
AND tmp.USER_ID=l.USER_ID
AND tmp.MaxDate=l.ACTION_DATE[/code]

neal.pressley
Starting Member

6 Posts

Posted - 2008-07-28 : 13:32:44

Hi TG, I never used cross apply, but can I use it in "SQL Server 8.00.760 - SP3 (Standard Edition)". when I run your query, I get error message "Incorrect syntax near the keyword 'cross'." Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-28 : 13:37:12

quote:
Originally posted by neal.pressley

Hi TG, I never used cross apply, but can I use it in "SQL Server 8.00.760 - SP3 (Standard Edition)". when I run your query, I get error message "Incorrect syntax near the keyword 'cross'." Thanks

Nope cross apply is only available in sql 2005 & above.

neal.pressley
Starting Member

6 Posts

Posted - 2008-07-28 : 13:51:34

Thank you so much TG and Visakh16!! it worked for me and I never knew that this forum can resolve the issue just in matter of minutes. Thanks again.

neal.pressley
Starting Member

6 Posts

Posted - 2008-07-28 : 13:51:38

Thank you so much TG and Visakh16!! it worked for me and I never knew that this forum can resolve the issue just in matter of minutes. Thanks again.

neal.pressley
Starting Member

6 Posts

Posted - 2008-07-28 : 14:24:56

Oops, there looks to be some problem Visakh. though the query above is able to show the result when we have logs of a user who was added, then deleted and then added again but it does not fetch those records who were added one time only.. I mean if we have a user for which we have only one record with action A.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-28 : 15:07:06

quote:
Originally posted by neal.pressley

Oops, there looks to be some problem Visakh. though the query above is able to show the result when we have logs of a user who was added, then deleted and then added again but it does not fetch those records who were added one time only.. I mean if we have a user for which we have only one record with action A.

perhaps this then:


SELECT l.*
FROM   USERS_ADD_DELETE_LOG l
INNER JOIN (
       SELECT CLIENT_ID,USER_ID,MAX(ACTION_DATE) AS MaxDate
       FROM USERS_ADD_DELETE_LOG 
       GROUP BY CLIENT_ID,USER_ID
       having right(max(convert(char(23), action_date, 121) + action), 1) = 'A'
       ) tmp
ON tmp.CLIENT_ID= l.CLIENT_ID
AND tmp.USER_ID=l.USER_ID
AND tmp.MaxDate=l.ACTION_DATE

Be One with the Optimizer
TG

neal.pressley
Starting Member

6 Posts

Posted - 2008-07-28 : 15:24:28

This was perfect.. this forum looks to be fabulous,, would love to hang around.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources