SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Last updated value for each column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dyahav
Starting Member

2 Posts

Posted - 11/13/2012 :  08:12:23  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8764 Posts

Posted - 11/13/2012 :  08:17:24  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

dyahav
Starting Member

2 Posts

Posted - 11/13/2012 :  09:07:16  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/13/2012 :  09:57:44  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000