SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 query to get latest 2 records for each group
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kt
Yak Posting Veteran

82 Posts

Posted - 08/21/2014 :  15:09:25  Show Profile  Reply with Quote
Hi,

select
DayRank = ROW_NUMBER() OVER(ORDER BY a.datedel DESC),
a.order,a.line,a.datedel,a.recpt,b.status,
b.item,b.t_sup
from historytbl a
inner join order b
on a.order = b.order
and a.line = b.line
and a.status =4
group by a.order,line,a.datedel,a.recpt,b.status,b.item,b.sup

The query is returned the results below.

Rank Order Line DateDel recpt item sup
----- ----- ---- ------ ------ ----- -----
1 aaa 10 2014-18-08 rc1 zzz123 1122
2 bbb 20 2014-08-08 rc2 zzz123 1122
3 ccc 30 2014-04-08 rc3 zzz123 1122
4 ddd 90 2014-08-11 rc6 yyy123 333
5 eee 10 2014-05-11 rc7 yyy123 333
5 fff 90 2014-02-11 rc8 yyy123 333
6 ggg 10 2014-05-10 rc9 qqq123 444
7 hhh 50 2014-04-10 rc0 qqq123 444
8 iii 10 2014-04-10 rc5 rrr123 555

However, I want to have the query only show most recent two records for each group of item and sup, please see the results I want below.


Rank Order Line DateDel recpt item sup
----- ----- ---- ------ ------ ----- -----
1 aaa 10 2014-18-08 rc1 zzz123 1122
2 bbb 20 2014-08-08 rc2 zzz123 1122

4 ddd 90 2014-08-11 rc6 yyy123 333
5 eee 10 2014-05-11 rc7 yyy123 333

6 ggg 10 2014-05-10 rc9 qqq123 444
7 hhh 50 2014-04-10 rc0 qqq123 444

Can any one help me?
Thanks
Kt

gbritton
Flowing Fount of Yak Knowledge

1109 Posts

Posted - 08/21/2014 :  15:17:08  Show Profile  Reply with Quote

select * from (

SELECT DayRank = ROW_NUMBER() OVER (
		Partition by a.recpt
        ORDER BY a.datedel DESC
        )
    ,a.
ORDER
    ,a.line
    ,a.datedel
    ,a.recpt
    ,b.STATUS
    ,b.item
    ,b.t_sup
FROM historytbl a
INNER JOIN order b ON a.order = b.order
    AND a.line = b.line
    AND a.STATUS = 4

GROUP BY a.order
    ,line
    ,a.datedel
    ,a.recpt
    ,b.STATUS
    ,b.item
    ,b.sup
	) q
Where DayRank <=2	
Go to Top of Page

kt
Yak Posting Veteran

82 Posts

Posted - 08/21/2014 :  15:49:09  Show Profile  Reply with Quote
thanks, but why Partition by a.recpt?, it can be anything, an number, such as:
REC040896
REC050182
REC050145
REC046338
REC042282

I ran your query but it was not return what i want . Can you advise?
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1109 Posts

Posted - 08/21/2014 :  17:16:51  Show Profile  Reply with Quote
I partitioned that way because it seems to match the grouping in your example. Also, because you need partitioning to make the Row_Number work, so the numbering is within the partition. That allows the "where dayrank <=2" at the end to do its job.

Since I don't have your data, it's difficult to go much further. If you post:

1. CREATE TABLE statements for all tables involved
2. INSERT INTO statements to populate the tables with test data.
3. The results you get when you run the query against the test tables
4. The results you want

then we can work on it more.

However, perhaps you can modify what I posted to make it work for you.
Go to Top of Page

kt
Yak Posting Veteran

82 Posts

Posted - 08/22/2014 :  13:47:11  Show Profile  Reply with Quote
thanks alot
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000