I've spent my whole day trying to come up with a solution for this problem, but I still have no clue:
I have in one table users the following fields:
and in another table city two fields:
What I have to do is to get the top rows from the users table as specified by the users_number field in city but first, ordering the fetched rows by score. The users quantity is far larger than the value specified by users_number.
My question is:
is there any possibility to select from the table users where criteria is number of rows in table city?
Here's an answer in T-SQL should that be of any help in figuring it out for Access?
declare @users table ( id int primary key clustered , name nvarchar(64) , city_id int , score int ) declare @city table ( id int primary key clustered , name nvarchar(64) , users_number int ) insert @city select 1, 'San Fran', 2 union select 2, 'NY', 5 insert @users select 1, 'Steve Fields', 1, 10 union select 2, 'Sarah Felcher', 1, 20 union select 3, 'Nick Yarnton', 2, 12 union select 4, 'Nigel Yardsman', 2, 12 union select 5, 'Nicki Yakatou', 2, 13 union select 6, 'Nicola Yates', 2, 23 union select 7, 'Steph Freeman', 1, 15 union select 8, 'Ned Yount', 2, 18 union select 9, 'Neil Yorke', 2, 1 select * from @city c left outer join ( select id, name, city_id, score , ROW_NUMBER() over (partition by city_id order by score desc) r from @users ) u on c.id = u.city_id where c.users_number >= u.r order by c.id, u.score desc