I have 2 tables connected with each other, for simplicity lets say ID of the 1st is connected to UserCreated_FK of the 2nd table.

Table A:

in the parallel table ( aka table B ) I have the following:

Result wanted: id.237 x10 last(might be sorted by date col I have).

no 240 (cause less than 10).

id.250 x10 last records(might be sorted by date col I have).

My task is to check which of those IDs(person'sIds) have more then 10 records on table B ( because table A is just the ids and names not the actual records of them.

So for example, ID no.237 got 19 records on table B I want to be able to pull the last 10 records of that user.

And again, the condition is only users that have more than 10 records, then to pull the last of 10 of those..

Hope I was understandable, thanks a lot, any help will be appreciated!

CodePudding user response：

You can join the two tables and then use analytic functions:

SELECT *
FROM   (
  SELECT a.id,
         a.name,
         b.col2,
         b.datetime,
         COUNT(*) OVER (PARTITION BY a.id) AS b_count,
         ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.datetime DESC) AS rn
  FROM   table_a a
         INNER JOIN table_b b
         ON (a.id = b.user_created_fk)
)
WHERE  b_count >= 10
AND    rn <= 10;

If you just want the values from table_b then you do not need to use table_a:

SELECT *
FROM   (
  SELECT b.*,
         COUNT(*) OVER (PARTITION BY b.user_created_fk) AS b_count,
         ROW_NUMBER() OVER (
           PARTITION BY b.user_created_fk ORDER BY b.datetime DESC
         ) AS rn
  FROM   table_b b
)
WHERE  b_count >= 10
AND    rn <= 10;

CodePudding user response：

The WITH clause is here just to generate some sample data and it is not a part of the answer.

WITH
    tbl_a AS
        (
            Select 237 "ID", 'Gal' "A_NAME" From Dual Union All
            Select 240 "ID", 'blah' "A_NAME" From Dual Union All
            Select 250 "ID", 'blah2' "A_NAME" From Dual 
        ),
    tbl_b AS
        (
            Select 237 "FK_ID", SYSDATE - (21 - LEVEL) "A_DATE" From Dual Connect By LEVEL <= 20 Union All
            Select 240 "FK_ID", SYSDATE - (6 - LEVEL) "A_DATE" From Dual Connect By LEVEL <= 5 Union All
            Select 250 "FK_ID", SYSDATE - (15 - LEVEL) "A_DATE" From Dual Connect By LEVEL <= 14 
        )
Select
    a.ID,
    a.A_NAME,
    b.TOTAL_COUNT "TOTAL_COUNT",
    b.RN "RN",
    b.A_DATE
From
    tbl_a a
Inner Join
    (   Select FK_ID, A_DATE, ROW_NUMBER() OVER(Partition By FK_ID Order By FK_ID, A_DATE DESC) "RN", Count(*) OVER(Partition By FK_ID) "TOTAL_COUNT" From tbl_b ) b ON(b.FK_ID = a.ID)
WHERE
  b.RN <= 10 And b.TOTAL_COUNT > 10

With your sample data the main SQL joins tbl_a with a subquery by FK_ID = ID. The subquery uses analytic functions to get total rows per FK_ID (in tbl_b) and gives row numbers (partitioned by FK_ID) to the dataset. All the rest is in the Where clause.
Result:

/*
        ID A_NAME TOTAL_COUNT         RN A_DATE  
---------- ------ ----------- ---------- ---------
       237 Gal             20          1 29-OCT-22 
       237 Gal             20          2 28-OCT-22 
       237 Gal             20          3 27-OCT-22 
       237 Gal             20          4 26-OCT-22 
       237 Gal             20          5 25-OCT-22 
       237 Gal             20          6 24-OCT-22 
       237 Gal             20          7 23-OCT-22 
       237 Gal             20          8 22-OCT-22 
       237 Gal             20          9 21-OCT-22 
       237 Gal             20         10 20-OCT-22 
       250 blah2           14          1 29-OCT-22 
       250 blah2           14          2 28-OCT-22 
       250 blah2           14          3 27-OCT-22 
       250 blah2           14          4 26-OCT-22 
       250 blah2           14          5 25-OCT-22 
       250 blah2           14          6 24-OCT-22 
       250 blah2           14          7 23-OCT-22 
       250 blah2           14          8 22-OCT-22 
       250 blah2           14          9 21-OCT-22 
       250 blah2           14         10 20-OCT-22 
*/

Regards...