SQL: select the 2 or 3 most recent transactions

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

Transact-SQL (2000)

SQL: select the 2 or 3 most recent transactions

Author

Topic

lcpx
Yak Posting Veteran

54 Posts

Posted - 2006-03-30 : 14:42:25

Hi,

Is there a way I can use SQL to select the most 2 or 3 recent transactions for each product. Let me give you an example, we have a table as:

Product_Code Order_Date
01 01/04/2006
01 01/03/2006
01 01/02/2006
01 01/01/2006
02 01/03/2006
02 01/02/2006
02 01/01/2006

I would like to know the last 2 order date for each product, it would display like:

Product_Code Order_Date
01 01/04/2006
01 01/03/2006
02 01/03/2006
02 01/02/2006

Can I do it with SQL. Thanks very much for your time and help!

Pete

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-30 : 14:54:23

Here's one way:


set nocount on
create table #tran (product_code int, order_date datetime)
insert #tran
select 01, '01/04/2006' union all
select 01, '01/03/2006' union all
select 01, '01/02/2006' union all
select 01, '01/01/2006' union all
select 02, '01/03/2006' union all
select 02, '01/02/2006' union all
select 02, '01/01/2006'

select	t.product_Code
	,t.Order_date
from	#tran t
where	order_date in 
		(select top 2 order_date 
		from #tran 
		where product_code = t.product_code
		order by Order_date)

output:
product_Code Order_date                                             
------------ -------------------------
1            2006-01-04 00:00:00.000
1            2006-01-03 00:00:00.000
2            2006-01-03 00:00:00.000
2            2006-01-02 00:00:00.000

Be One with the Optimizer
TG

jhermiz

3564 Posts

Posted - 2006-03-30 : 15:18:45

I dont think that will bring back the correct data since you did not order Order_date DESC. TOP 2 doesnt ensure it will pick up the highest date values, it only ensures that it picks up 2 records.

Using the same code just tack on: DESC


set nocount on
create table #tran (product_code int, order_date datetime)
insert #tran
select 01, '01/04/2006' union all
select 01, '01/03/2006' union all
select 01, '01/02/2006' union all
select 01, '01/01/2006' union all
select 02, '01/03/2006' union all
select 02, '01/02/2006' union all
select 02, '01/01/2006'

select	t.product_Code
	,t.Order_date
from	#tran t
where	order_date in 
		(select top 2 order_date 
		from #tran 
		where product_code = t.product_code
		order by Order_date DESC)

/*output:
product_Code Order_date                                             
------------ -------------------------
1            2006-01-04 00:00:00.000
1            2006-01-03 00:00:00.000
2            2006-01-03 00:00:00.000
2            2006-01-02 00:00:00.000*/

DROP TABLE #tran

Make sure you drop #tran as well

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

lcpx
Yak Posting Veteran

54 Posts

Posted - 2006-03-30 : 17:53:42

Thanks so much for your help, I'll try it later and let you know if it works. Cheers!

Just to make you aware that the SQL works amazingly well. Thanks a lot!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-31 : 07:55:34

Also refer point 2
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources