December 17, 2008 at 1:17 pm
I have data like this
empno address dob joindate timecolumn
now the empno are repeating for any kind of change in columns but timestamp will also increse...I want to fetch only the latest record for every employee....
MAX(TIMESTAMP)
but if i give this using a group by i still get repeated empno..
what shud be the query for this?
any help?
Thanks [/font]
December 17, 2008 at 1:58 pm
Something like this will work:
SELECT
a.empno,
a.address,
a.dob,
a.joindate,
a.timestamp
FROM
MyTable a
INNER JOIN
(SELECT
empno,
MAX(timestamp) as timestamp
FROM
MyTable
GROUP BY
empno) as b ON a.empno = b.empno AND a.timestamp = b.timestamp
December 17, 2008 at 2:26 pm
You could use something like:
;With cteEmp (EmpNo, AddressLine, Dob, JoinDate, timestamp, rn)
As (
Select a.empno
,a.address
,a.dob
,a.joindate
,a.timestamp
,row_number() over(Partition By a.empno Order By a.timestamp desc)
From MyTable a)
Select *
From cteEmp
Where rn = 1;
This is untested - and you might need a modification on the partition statement in the over clause, but it should give you the idea.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply