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
 General SQL Server Forums
 New to SQL Server Programming
 query to get latest 2 records for each group

Author  Topic 

kt
Yak Posting Veteran

88 Posts

Posted - 2014-08-21 : 15:09:25
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
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-21 : 15:17:08
[code]
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
[/code]
Go to Top of Page

kt
Yak Posting Veteran

88 Posts

Posted - 2014-08-21 : 15:49:09
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
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-21 : 17:16:51
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

88 Posts

Posted - 2014-08-22 : 13:47:11
thanks alot
Go to Top of Page
   

- Advertisement -