"Another" Latest Record of a group

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 2005 Forums

Transact-SQL (2005)

"Another" Latest Record of a group

Author

Topic

lb6688
Starting Member

18 Posts

Posted - 2012-06-19 : 14:24:14

I have a Inventory Transaction table (MS SQL Server 2008) look like the following:

Serial_Number    Tran_Type    Tran_Date
111111                 2                 1-1-2012
111111                 3                 1-2-2012
111111                 4                 1-3-2012
222222                 2                 1-2-2012
222222                 3                 1-6-2012
333333                 2                 1-7-2012
333333                 3                 1-8-2012
333333                 2                 1-9-2012
444444                 2                 1-9-2012

I know how to get the last/latest record of each group (group by Serial_number) which will give me the result-set of:
111111                 4                 1-3-2012
222222                 3                 1-6-2012
333333                 2                 1-9-2012
444444                 2                 1-9-2012

However, I need to get the latest record of each group if the latest record Transaction_Type is 2 or 3, if the latest record transaction_type is not in (2,3) then I do not want it to show:
So the result-set I want to get look like following:
222222                 3                 1-6-2012
333333                 2                 1-9-2012
444444                 2                 1-9-2012

Can you show me an efficient T-SQL (The table can have a few millions of records)?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-06-19 : 14:35:55

Not sure how effectient but

DECLARE @Table TABLE (SerialNumber int,TranType int,TranDate date)

INSERT INTO @Table
VALUES
(111111, 2,'1-1-2012'),
(111111 , 3,'1-2-2012'),
(111111 , 4,'1-3-2012'),
(222222 , 2,'1-2-2012'),
(222222 , 3,'1-6-2012'),
(333333 , 2,'1-7-2012'),
(333333 , 3,'1-8-2012'),
(333333 , 2,'1-9-2012'),
(444444 , 2,'1-9-2012')

select t.SerialNumber,MAX(trandate) as TranDate,MAX(TranType) as TranType
from @Table t
where t.SerialNumber in (select serialnumber from @Table group by SerialNumber having MAX(trantype) IN(2,3))
group by t.SerialNumber

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-19 : 15:44:12

[code]
SELECT t.*
FROM table t
inner join (select Serial_Number,MAX(tran_date) as latest
FROM table
GROUP BY Serial_Number
)t1
ON t1.Serial_number = t.Serial_Number
AND t1.latest = t.tran_date
WHERE t.Tran_Type IN (2,3)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lb6688
Starting Member

18 Posts

Posted - 2012-06-19 : 16:32:23

Thanks both of you taking time to reply. I do not have a large data set (yet) to benchmark the performance, however, my guess it will be the same (I have a feeling the both produce the same execution plan).

Thanks again.

LB

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-19 : 18:47:58

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-20 : 08:20:02

You can do it this way too:


--Creating Table

Create Table Ex
(Serial_Number int,
 Tran_Type int,
 Tran_Date Date )


--Inserting Sample Data

Insert into Ex
Select 111111,                 2,                 '2012/01/01'
Union ALL
Select 111111,                 3,                 '2012/01/02'
Union ALL
Select 111111,                 4,                 '2012/01/03'
Union ALL
Select 222222,                 2,                 '2012/01/02'
Union ALL
Select 222222,                 3,                 '2012/01/06'
Union ALL
Select 333333,                 2,                 '2012/01/07'
Union ALL
Select 333333,                 3,                 '2012/01/08'
Union ALL
Select 333333,                 2,                 '2012/01/09'
Union ALL
Select 444444,                 2,                 '2012/01/09'


--Query For Your Requirement

Select Serial_Number, Tran_Type, Tran_Date from
	(Select *, ROW_NUMBER() Over (Partition By Serial_Number Order By Tran_Date Desc ) As rn From Ex) As a
Where rn = 1 AND Tran_Type IN (2, 3)

N 28° 33' 11.93148"
E 77° 14' 33.66384"

lb6688
Starting Member

18 Posts

Posted - 2012-06-20 : 13:50:53

Thanks, inu.vijayan, I think your approach may be the winner for the best performance among the three options. I will run some test to let you know.

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-21 : 00:07:43

quote:
Originally posted by lb6688

Thanks, inu.vijayan, I think your approach may be the winner for the best performance among the three options. I will run some test to let you know.

You're Welcome

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources