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.
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_supfrom historytbl ainner join order bon a.order = b.orderand a.line = b.lineand a.status =4group by a.order,line,a.datedel,a.recpt,b.status,b.item,b.supThe query is returned the results below.Rank Order Line DateDel recpt item sup----- ----- ---- ------ ------ ----- -----1 aaa 10 2014-18-08 rc1 zzz123 11222 bbb 20 2014-08-08 rc2 zzz123 11223 ccc 30 2014-04-08 rc3 zzz123 11224 ddd 90 2014-08-11 rc6 yyy123 3335 eee 10 2014-05-11 rc7 yyy123 3335 fff 90 2014-02-11 rc8 yyy123 3336 ggg 10 2014-05-10 rc9 qqq123 4447 hhh 50 2014-04-10 rc0 qqq123 4448 iii 10 2014-04-10 rc5 rrr123 555However, 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 11222 bbb 20 2014-08-08 rc2 zzz123 11224 ddd 90 2014-08-11 rc6 yyy123 3335 eee 10 2014-05-11 rc7 yyy123 3336 ggg 10 2014-05-10 rc9 qqq123 4447 hhh 50 2014-04-10 rc0 qqq123 444Can any one help me?ThanksKt |
|
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_supFROM historytbl aINNER JOIN order b ON a.order = b.order AND a.line = b.line AND a.STATUS = 4GROUP BY a.order ,line ,a.datedel ,a.recpt ,b.STATUS ,b.item ,b.sup ) qWhere DayRank <=2 [/code] |
|
|
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: REC040896REC050182REC050145REC046338REC042282I ran your query but it was not return what i want . Can you advise? |
|
|
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 involved2. INSERT INTO statements to populate the tables with test data.3. The results you get when you run the query against the test tables4. The results you wantthen we can work on it more.However, perhaps you can modify what I posted to make it work for you. |
|
|
kt
Yak Posting Veteran
88 Posts |
Posted - 2014-08-22 : 13:47:11
|
thanks alot |
|
|
|
|
|
|
|