Return records based on a value in another column from another table.

JHi,
I have two tables , TABLE A and TABLE B. In table A, I'm specifying a type of code and, in the other column (NoRecords), the amount of records to return from TABLE B for that code.
For instance, code A would get 2 records from TABLE B. Code B, 3.
The records returned should be the highest values for each group.



TABLE A
id      Code    NoRecords
1      A              2
2      B             3
3      C             2


TABLE B
id      QNumber        Mark      Code
1         1                        3.5                A
2         2                        2.5               A
3        3                       3.0               A
4        4                        4.0                A
5        5                       3.5               B
6        6                       1.5               B
7        7                       2.5                B
8        8                        4.0                B
9        9                        0.5                C
10        10                        2.0               C
11       11                        2.5            C
12       12                         2.0      C


I got table B ordered as I want it using a window function, but I can't work out how to get the (top) number of records per each group specified in TABLE A.

SELECT Code, Mark, myorder
	FROM 
		( SELECT Code, Mark, ROW_NUMBER() OVER (PARTITION BY Code ORDER BY Mark DESC )
             AS myorder
		FROM TABLE_B 
	) tmp 

Open in new window


Code Mark   MyOrder
A      4.0            1
A      3.5            2
A      3.0            3
A      2.5            4
B      4.0            1
B      3.5            2
B      2.5            3
B      1.5            4
C      2.5            1
C      2.0            2
C      2.0            3
C      0.5            4


This is the result I'm after.

Result

Code      Mark          QNumber
A              4.0                        4
A              3.5                            1
B              4.0                       8                
B              3.5                       5
B             2.5                       7
C             2.5                    11
C             2.0                     10


Many thanks
Adrian CrossAsked:
Who is Participating?
 
_agx_Commented:
Just join it back to Table_A on the "Code" and filter on row number <= the number of records you want to retrieve

SELECT b.Code, b.Mark, b.myorder
	FROM 
		( SELECT Code, Mark, ROW_NUMBER() OVER (PARTITION BY Code ORDER BY Mark DESC )
             AS myorder
		FROM TABLE_B 
	) b INNER JOIN table_A a ON a.Code = b.Code AND b.MyOrder <= noRecords

Open in new window

0
 
Adrian CrossAuthor Commented:
Great!! very fast answer!!

I can't believe I didn't see that.

Many thanks
0
 
_agx_Commented:
Glad it helped.  Always easier to spot when you're not in the the thick of it :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.