how to apply foreach loop in sql query

Question

Check the sql query bellow. Here in SearchedUserItems table there is a foreign key column named SearchedUserID which is primary key in another table called SearchedUsers. So basically in this query i am saying give me top 100 items from table SearchedUserItems which items have matching SearchedUserID from inner query list select top 10 SearchedUserID from SearchedUsers. So this gives me top 100 items from all top 10 SearchedUserID matched but my goal is: I want to get top 100 items from each SearchedUserID matched from SearchedUsers table. From current query i am getting total 100 matched items only but i need 100 items from each matched SearchedUserID from SearchedUsers table. How can i apply this for each thing in this query?

If you are not clear about question plz ask me. I tried my best to explain

select top 100 * from SearchedUserItems where SearchedUserID in (
select top 10 SearchedUserID from SearchedUsers
)

Above query result picture

Use window functions (`row_number() ...`), do not forget specify ordering for all top N functions. — Arvo, Sep 14 '21 at 09:17
You are getting ten random SearchedUserIds, but not the top ten as defined by some quantity - you have no ordering criteria. — Stu, Sep 14 '21 at 09:17
I really don't understand what you are trying to explain here. Some sample data and expected results will *really* help us help you here. Also, your title mentions looping; this is something you should very likely be avoiding. SQL is a set based language, so it performs awfully at iterative tasks (and well, at set based ones, unsurprisingly). Finally, use of a `TOP` without an explicit `ORDER BY`, is generally a bad idea. This will return *x* arbitrary rows, and those rows can be different **every time** you run said query. — Thom A, Sep 14 '21 at 09:18

VBoka · Accepted Answer · 2021-09-14T10:29:01.440

In my example you have two tables: A & B. They are connected over column id from table A and A_id from table B. In your question you are looking for top 100 records for each of top 10 records and in my example I am looking for top 2 records for each of top 2 records (I hope you understood this...)

select *
from ( select A.*
              , ROW_NUMBER() OVER(PARTITION BY id ORDER BY  id DESC) rn
       from A 
       where ID in (select top 2 A_id 
                    from B 
                    group by A_id
                    order by A_id asc)) Table_C
where rn <= 2

Here you can see the query in action: DEMO

how to apply foreach loop in sql query

1 Answers1