Last updated value for each column

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

Last updated value for each column

Author

Topic

dyahav
Starting Member

2 Posts

Posted - 2012-11-13 : 08:12:23

Hi,
I have a table that contains the following columns:

id int --The id is not a primary key.
updateTime time
fname string
lname string

The fname and the lname are nullable and in some cases one of them can be null.

For each id I need to get the last updated fname and last updated l name.

For example:

id, updateTime, fname, lname
----------------------------
1 1/1/12 aa null
1 1/2/12 null bb
1 1/1/11 dd kk
2 1/1/11 ss ll

The result will be:

id, updateTime, fname, lname
----------------------------
1 1/2/12 aa bb
2 1/1/11 ss ll

Thanks
dyahav

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-11-13 : 08:17:24

select id, updateTime, fname, lname from
(select row_number() over (partition by id order by updateTime DESC) as rnum,* from YourTable)dt
where rnum=1

Too old to Rock'n'Roll too young to die.

dyahav
Starting Member

2 Posts

Posted - 2012-11-13 : 09:07:16

quote:
Originally posted by webfred

select id, updateTime, fname, lname from
(select row_number() over (partition by id order by updateTime DESC) as rnum,* from YourTable)dt
where rnum=1

Too old to Rock'n'Roll too young to die.

Hi,
Thanks but I see that your solution gives the last updated row. In my case If a value is null, it should be taken from other old row.
Please look at the following example:
id, updateTime, fname, lname
----------------------------
1 1/1/12 aa null
1 1/2/12 null bb
1 1/1/11 dd kk
2 1/1/11 ss ll

The result will be:

id, updateTime, fname, lname
----------------------------
1 1/2/12 aa bb
2 1/1/11 ss ll

Thanks
dyahav

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-13 : 09:57:44

A little messy and convoluted - probably there are simpler ways to do this...

SELECT
	a.id,
	CASE WHEN c.updatetime > b.updatetime THEN c.updatetime ELSE b.updatetime END AS updatetime,
	b.fname,
	c.lname
FROM
	(SELECT DISTINCT id FROM  Tbl) a
	OUTER APPLY 
	(
		SELECT TOP 1 b.updatetime,b.fname FROM Tbl b 
		WHERE b.id = a.id AND b.fname IS NOT NULL
		ORDER BY updatetime DESC
	)b
	OUTER APPLY 
	(
		SELECT TOP 1 c.updatetime,c.lname FROM Tbl c 
		WHERE c.id = a.id AND c.lname IS NOT NULL
		ORDER BY updatetime DESC
	)c

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources