Order by of records of a table in SQL Server I have the following tables:

Customer (ID, Name, CountryID)

Country (ID, Name, Code)



From the web application, user selects the number of Customers and the Countries then based on these parameters the system shows the result.

For example, user selects the number of customers as 2 and countries as US and UK then the system must show the result as follow:

CustomerID    CustomerName    Country
--------------------------------------
1             A               US
2             B               US
3             C               UK
4             D               UK
.
.
.


How can we achieve the above result?
Test

WITH
cte1 AS ( SELECT 'US' country, 10 cnt
          UNION ALL
          SELECT 'UK', 2
          -- UNION ALL
          -- ... 
        ),
cte2 AS ( SELECT customer.*, 
                 country.name country, 
                 ROW_NUMBER() OVER (PARTITION BY country.name 
                                    ORDER BY customer.id) rn
          FROM customer
          JOIN country on customer.country_id = country.id 
        )
SELECT cte2.id CustomerID, cte2.name CustomerName, cte2.Country
FROM cte2
JOIN cte1 ON cte1.country = cte2.country
WHERE cte2.rn &lt;= cte1.cnt