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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need help to produce the record by latest date

Author  Topic 

Idyana
Yak Posting Veteran

96 Posts

Posted - 2014-04-12 : 17:20:51
My table and data as following,


declare @jobHistory table (jobHistoryID int, groupID int, Company varchar(100),
Position varchar(100), startDte datetime) ;
/*
jobHistoryID is a Primary Key
*/

insert into @jobHistory values(1, 40, 'STATSCHIPPAC (M) SDN BHD', 'SENIOR TECHNICIAN', '19960308');
insert into @jobHistory values(2, 12, 'Intel Product (M) Sdn Bhd', 'Engineer (Test Development)', '20050601');
insert into @jobHistory values(3, 40, 'MAYFLOWER CAR RENTAL', 'ASSISSTANT OPERATION', '20050825');
insert into @jobHistory values(4, 21, 'SHIN YANG TRADING', 'ACCOUNT ASSISTANT', '20070723');
insert into @jobHistory values(5, 55, 'CITIGROUP', 'CUSTOMER SERVICE OFFICER', '20090112');
insert into @jobHistory values(6, 12, 'Tyco Electronics (M) Sdn Bhd', 'Accounts Clerk', '19970925');


Looking for help to produce the record by latest date. In this case the latest date is startDte

My expected result shown as follow,


jobHistoryID groupID Company Position startDte
-----------------------------------------------------------------
2 12 Intel Product (M) Sdn Bhd Engineer (Test Development) 2005-06-01 00:00:00.000
3 40 MAYFLOWER CAR RENTAL ASSISSTANT OPERATION 2005-08-25 00:00:00.000
4 21 SHIN YANG TRADING ACCOUNT ASSISTANT 2007-07-23 00:00:00.000
5 55 CITIGROUP CUSTOMER SERVICE OFFICER 2009-01-12 00:00:00.000
]

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-13 : 03:09:45
[code]

select
jobHistoryID
,groupID
,Company
,Position
,startDte
from
(select
jobHistoryID
,groupID
,Company
,Position
,startDte
,Row_Number() OVER(Partition BY groupID Order by startDte Desc) as rn
from
@jobHistory)A
WHERE A.rn=1
[/code]


sabinWeb MCP
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-13 : 03:10:07
[code]
jobHistoryID groupID Company Position startDte
2 12 Intel Product (M) Sdn Bhd Engineer (Test Development) 2005-06-01 00:00:00.000
4 21 SHIN YANG TRADING ACCOUNT ASSISTANT 2007-07-23 00:00:00.000
3 40 MAYFLOWER CAR RENTAL ASSISSTANT OPERATION 2005-08-25 00:00:00.000
5 55 CITIGROUP CUSTOMER SERVICE OFFICER 2009-01-12 00:00:00.000
[/code]


sabinWeb MCP
Go to Top of Page

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-04-14 : 00:29:31
I think this is another best way.......

SELECT * FROM
(SELECT jobHistoryID,
groupID,
Company,
Position,
(CASE WHEN MAX(startDte)OVER (PARTITION BY groupID) = startDte THEN startDte END) AS StartDate FROM @jobHistory)
AS A WHERE A.StartDate IS NOT NULL ORDER BY jobHistoryID


---------------
Murali Krishna

You live only once ..If you do it right once is enough.......
Go to Top of Page

Idyana
Yak Posting Veteran

96 Posts

Posted - 2014-04-14 : 02:46:09
Tq to both of you. Let me try and will share the result
Go to Top of Page
   

- Advertisement -