getting last inserted value - 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

getting last inserted value

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-03-03 : 04:15:58

hi,

this is my spc:
select top 1 u.userid,
u.user_name,
u.password,
c.code_description as role_code,
u.expiry_date,
u.created_date,
u.active,
convert(varchar,v. user_date, 103) + ' ' + right('0' + stuff(right(convert(varchar,v.user_date, 109), 14), 8, 4, ''), 10) as user_date,
v.operation,h.user_name as updatedby
from [usermaster] u inner join [codeMaster] c
on 'sp'=c.code inner join [HRUser_developerlog] v on u.userid=v.inserted_id and v.operation='update' inner join [usermaster] h on v.userid=h.userid
where u.userid = '3' order by v. user_date

if i use it gives the v.user_date as fist modified date it is not giving last modified date.

select * from HRUser_developerlog

user_date operat userid
ion
2007-01-25 14:28:17.000 insert 1
2007-01-24 13:02:18.093 insert 4
2007-03-03 11:30:29.310 update 2
2007-03-03 11:30:55.373 insert 3
2007-03-03 11:31:31.717 insert 26
2007-01-25 14:28:17.000 insert 3
2007-03-03 11:43:39.733 update 26
2007-03-03 11:48:04.543 delete 3
2007-03-03 14:26:22.420 update 3
2007-03-03 14:27:00.280 update 3
2007-03-03 14:27:12.013 update 2
2007-03-03 14:27:35.763 update 1
2007-02-08 14:28:17.030 update 2
2007-03-03 14:27:55.967 update 3
2007-03-03 14:29:18.827 update 3
2007-03-03 14:30:52.983 update 3

so it has to show
2007-03-03 14:30:52.983
but it shows the first updated id only
2007-03-03 14:26:22.420

please help me to get my need

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-03-03 : 04:39:35

please sny one reply me it's very urgent please

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-03-03 : 04:45:40

--this will give you one row for each operation,userid and latest date

select max(date),operation,userid
from table1
group by operation,userid

now, you can join this in your query for details, on userid, something like...

select .... from masterTable m
join (select max(date),operation,userid
from table1
group by operation,userid) a
on m.userid=a.userid

for operation specific, put a having clause in the first query

HTH

--------------------
keeping it simple...

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-03-03 : 04:52:23

hi jen ,canu please modify my coding

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-03-03 : 05:09:05

not sure if this fits in your schema... also use variables in your filter values


select top 1 u.userid,
	u.user_name,
	u.password,
	c.code_description as role_code,
	u.expiry_date,
	u.created_date,
	u.active,
	convert(varchar,v. user_date, 103) + ' ' + right('0' + stuff(right(convert(varchar,v.user_date, 109), 14), 8, 4, ''), 10) as user_date,
	v.operation,h.user_name as updatedby
from [usermaster] u inner join [codeMaster] c 
	on u.code=c.code 
inner join (select max(date),operation,userid from hruser_developerlog group by operation,userid having operation='update') v 
on u.userid=v.inserted_id
inner join [usermaster] h on v.userid=h.userid
where u.userid = '3'  and u.code='sp' 
order by v. user_date

HTH

--------------------
keeping it simple...

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-03-03 : 05:34:52

hi jen i used this:

select top 1 u.userid,
u.user_name,
u.password,
c.code_description as role_code,
u.expiry_date,
u.created_date,
u.active,
convert(varchar,v. user_date, 103) + ' ' + right('0' + stuff(right(convert(varchar,v.user_date, 109), 14), 8, 4, ''), 10) as user_date,
v.operation,h.user_name as updatedby
from [usermaster] u inner join [codeMaster] c
on u.code=c.code
inner join (select max(v.user_date) from [HRUser_developerlog] v group by user_datev having operation='update' )
on u.userid=v.inserted_id
inner join [usermaster] h on v.userid=h.userid
where u.userid = '3' and u.code='sp'
order by v. user_date

but it shows the following error:
Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'select'.
Server: Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'on'.

quote:

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-03-03 : 05:49:50

you can't get a max(userdate) because that will give you the latest date from EVERYONE... if you want, add another filter

also, how do you join it with just the date?

another way is...

select....,(select max(userdate) from hruser_developerlog where userid=u.userid and operation='updated')
from....

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources