get last updated datetime - SQL Server Forums

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

get last updated datetime

Author

Topic

bluemagic74
Starting Member

15 Posts

Posted - 2012-01-05 : 16:53:56

Hi we have more than 1 tables that has lastupdateddatetime column. how can I find out a user's last time entered in system. Thanks!

we are using sql server 2005 version

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-05 : 17:25:54

I think the simplest may be to union results from all the relevant tables and then pick the max out of that.

SELECT userId, MAX(MaxUpdateTime) AS MaxUpdateTime
FROM
(
	SELECT userId, MAX(updatetime) AS MaxUpdateTime FROM Table1 GROUP BY userId
	UNION ALL
	SELECT userId, MAX(updatetime) AS MaxUpdateTime FROM Table2 GROUP BY userId
	UNION ALL
	SELECT userId, MAX(updatetime) AS MaxUpdateTime FROM Table3 GROUP BY userId
) s
GROUP BY userId;

bluemagic74
Starting Member

15 Posts

Posted - 2012-01-09 : 15:31:05

Thanks. This query works ,but I am getting more than 1 row for user. How to get only very last Updated time per user.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-09 : 15:33:55

quote:
Originally posted by bluemagic74

Thanks. This query works ,but I am getting more than 1 row for user. How to get only very last Updated time per user.

Ummm not if you ran the query listed by sunita you aren't

Not possible

Post the EXACT Code you ran

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

bluemagic74
Starting Member

15 Posts

Posted - 2012-01-09 : 20:15:43

Here you go

CREATE TABLE [dbo].[Table1](
[USER_NB] [int] NOT NULL,

[LSTUPDT_TS] [datetime] NOT NULL)

CREATE TABLE [dbo].[Table2](
[USER_NB] [int] NOT NULL,

[LSTUPDT_TS] [datetime] NOT NULL)

CREATE TABLE [dbo].[Table3](
[USER_NB] [int] NOT NULL,

[LSTUPDT_TS] [datetime] NOT NULL)

USER_NB LSTUPDT_TS
1 11/20/07 6:04
2 11/21/07 6:09
1 11/22/07 7:06
10 11/23/07 6:25
1 11/24/07 6:47
5 11/25/07 7:20
2 11/26/07 4:58
5 11/27/07 6:09
2 11/28/07 6:16
1 11/29/07 6:18
3 11/29/07 18:58
3 11/30/07 6:42

I am running below query to get very last updated date time.

SELECT USER_NB, Max(MaxUpdateTime)
FROM
(
SELECT USER_NB, MAX(LSTUPDT_TS) AS MaxUpdateTime FROM Table1 GROUP BY USER_NB
UNION ALL

SELECT USER_NB, MAX(LSTUPDT_TS) AS MaxUpdateTime FROM Table 2 GROUP BY USER_NB
UNION ALL
SELECT USER_NB, MAX(LSTUPDT_TS) AS MaxUpdateTime FROM Table 3 GROUP BY USER_NB

) s
GROUP BY S.USER_NB, s.MaxUpdateTime

But the output shows more than one row per user.

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-10 : 06:59:01

Remove the grouping on the MaxUpdateTime.

SELECT USER_NB, Max(MaxUpdateTime)
FROM
(
SELECT USER_NB, MAX(LSTUPDT_TS) AS MaxUpdateTime FROM Table1 GROUP BY USER_NB
UNION ALL

SELECT USER_NB, MAX(LSTUPDT_TS) AS MaxUpdateTime FROM Table 2 GROUP BY USER_NB
UNION ALL
SELECT USER_NB, MAX(LSTUPDT_TS) AS MaxUpdateTime FROM Table 3 GROUP BY USER_NB

) s
GROUP BY S.USER_NB  , s.MaxUpdateTime

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources