CODEWITHSUNDEEP
sqlsql-serversql-server-2005group-by
sqlnoob
sqlnoob

Reputation: 13

Query to get specific row MS SQL Server:

I have got a strange request. In the below table i have vendorno., act no., part no., and movein_date. i want to select the row of the part no. which came last before U13 came in (have closest past movein_date of the movein_date for U13 partno) for each act_no.

Sample:

1.  Vendor_No Act_num Part_Num Movein_Date
2.  708     3321386 T01     2/19/2012
3.  708     3321386 U13     2/15/2012
4.  708     3321386 U01     2/1/2012
5.  708     3321386 T14     1/31/2012
6.  708     3321386 Y10     1/30/2012
7.  708     3321386 U05     12/31/2011
8.  106     3852820 U13     12/30/2012
9.  106     3852820 T28     12/28/2012
10. 106     3852820 U09     11/12/2012
11. 106     3852820 U01     10/24/2012
12. 106     3852820 T09     4/21/2012
13. 472     3925527 U14     8/3/2012
14. 472     3925527 U13     7/3/2012
15. 472     3925527 T01     1/6/2012
16. 472     3925527 T05     1/6/2012
17. 472     3925527 T14     6/12/2011
18. 472     3925527 U01     5/31/2011
19. 685     4938653 U13     1/28/2013
20. 685     4938653 W20     1/20/2013
21. 685     4938653 U01     1/2/2013
22. 685     4938653 W17     12/25/2012
23. 685     4938653 T05     12/20/2012

Result i need:

1.  Vendor_No   Act_num Part_Num    Movein_Date
2.  708 3321386 U01 2/1/2012
3.  106 3852820 T28 12/28/2012
4.  472 3925527 T01 1/6/2012
5.  472 3925527 T05 1/6/2012
6.  685 4938653 W20 1/20/2013

Please let me know if i am not clear.

Upvotes: 0

Views: 569

Answers (2)

Hart CO
Hart CO

Reputation: 34774

One way to do this is to use a RANK function:

SELECT *
FROM (SELECT *, RANK () OVER(PARTITION BY ACT_NUM ORDER BY Movein_Date DESC)'RowRank'
      FROM Table1
      )a
JOIN (SELECT *, RANK () OVER(PARTITION BY ACT_NUM ORDER BY Movein_Date DESC)'RowRank'
      FROM Table1
      )b
 ON a.RowRank = b.RowRank +1
AND a.ACT_NUM = b.ACT_NUM
 AND b.Part_Num = 'U13'

The RANK basically just numbers the lines based on the Movein_Date, then you can get a one line offset by JOINING on the RowRank + 1.

Here is a demo: SQL Fiddle Note: Ignore the 'ID' field, it's just the line numbers copied over from your example.

Edit: Missed the per account number part, fixed.

Upvotes: 0

Jon Spokes
Jon Spokes

Reputation: 2599

I haven't got a database to hand, but off the top of my head something like this should get you the basic data you're after (you'll need to change the tablename from myTable): 

Select Act_num, max(Movein_date) maxdate
from myTable 
where Movein_date < (Select max(Movein_date) from myTable where part_num = 'U13') 
and part_num != 'U13'
group by Act_num

Then joining it back to itself should give you the final dataset : 

Select * 
from myTable tab1 
inner join (
      Select Act_num, max(Movein_date) maxdate
      from myTable 
      where Movein_date < (Select max(Movein_date) from myTable where part_num = 'U13') 
      group by Act_num
) tab2 
on tab1.Act_num = tab2.Act_Num and tab1.Movein_date = tab2.maxdate

Upvotes: 2

Related Questions