Find last record ........ - 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

Find last record ........

Author

Topic

yaman
Posting Yak Master

213 Posts

Posted - 2008-04-14 : 05:43:10

sno Empid Address Date
------------------------------------------------
1 100 bhopal 2008-01-02 00:00:00.000
2 100 indore 2008-02-02 00:00:00.000
3 100 gwalior 2008-03-03 00:00:00.000
4 101 gwalior 2008-01-02 00:00:00.000
5 101 Indore 2008-02-02 00:00:00.000
6 102 bhopal 2008-01-01 00:00:00.000
------------------------------------------------
I want last update Address of employee from this table like

100 Gwalior
101 Indore
102 Bhopal

pls help me out ...............

Yaman

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-14 : 05:46:23

select *
from tbl t
where t.sno = (select max(sno) from tbl t2 where t2.Empid = t.Empid)

select t.*
from tbl t
join (select Empid, max(sno) from tbl group by Empid) t2
on t.Empid = t2.Empid -- not needed if sno is unique but better to include it
and t.sno = t2.sno

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-14 : 07:08:22

If you're using sql 2005 you can even use the ROW_NUMBER() function for this:-

SELECT t.Empid,t.Address
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Empid ORDER BY Date DESC) AS RowNo,
sno,
Empid,
Address,
Date
FROM YourTable
)t
WHERE t.RowNo=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources