Order by of records of a table in SQL Server

Question

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?

Answer 1

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 <= cte1.cnt

Answer 2

One of the best options will be through UNION ALL:

SELECT TOP(2) c.ID AS [CustomerID], c.[Name] AS [CustomerName], cc.Code AS [Country]
FROM Customer AS c
INNER JOIN Country AS cc ON c.CountryID=cc.ID
WHERE cc.Code='US'
UNION ALL
SELECT TOP(2) c.ID AS [CustomerID], c.[Name] AS [CustomerName], cc.Code AS [Country]
FROM Customer AS c
INNER JOIN Country AS cc ON c.CountryID=cc.ID
WHERE cc.Code='UK';

If necessary, you can generate the code dynamically

Answer 3

Try with this

--Number of Customer
DECLARE @NumerOfCustomer INT=2
--you need to receive the countrys list
DECLARE @Countrys NVARCHAR(MAX)='1,2'

--Simulated Table
DECLARE @Customer TABLE (ID INT, Name NVARCHAR(50), CountryID INT)
DECLARE @Country TABLE (ID INT, Name NVARCHAR(50), Code INT)

--INSERT Customer
INSERT INTO @Customer VALUES(1,'Test1',1)
INSERT INTO @Customer VALUES(2,'Test2',1)
INSERT INTO @Customer VALUES(3,'Test3',1)
INSERT INTO @Customer VALUES(4,'Test4',2)
INSERT INTO @Customer VALUES(5,'Test5',2)
INSERT INTO @Customer VALUES(6,'Test6',2)
INSERT INTO @Customer VALUES(7,'Test7',3)
INSERT INTO @Customer VALUES(8,'Test8',3)

--INSERT Country
INSERT INTO @Country VALUES(1,'UK',1)
INSERT INTO @Country VALUES(2,'ES',2)
INSERT INTO @Country VALUES(3,'MX',1)

--How many countries are received by means of a function Split
DECLARE @Count INT=(SELECT COUNT(*) FROM dbo.Split(@Countrys,','))

--We insert the result here
DECLARE @TableAux TABLE(CustomerID INT,CustomerName NVARCHAR(50),Country NVARCHAR(50))
--This is a Aux count
DECLARE @Help INT=1
--The Country Aux
DECLARE @CountryAux NVARCHAR(10)
WHILE @Help <=@Count
BEGIN

SELECT @CountryAux=(SELECT Data FROM dbo.Split(@Countrys,',') WHERE ID=@Help)

INSERT INTO @TableAux
SELECT TOP (@NumerOfCustomer)  CR.ID AS CustomerID,CR.[Name] AS CustomerName,CT.Name AS Country
FROM @Customer CR
INNER JOIN @Country CT ON CT.ID=CR.CountryID
WHERE CT.ID=@CountryAux

SET @Help=@Help+1
--RESET 
SET @CountryAux=''
END


--Select ALL Result
SELECT * FROM @TableAux

IF you receive the country how string then

--Number of Customer
DECLARE @NumerOfCustomer INT=2
--you need to receive the countrys list
--
DECLARE @Countrys NVARCHAR(MAX)='UK,ES'

--Simulated Table
DECLARE @Customer TABLE (ID INT, Name NVARCHAR(50), CountryID INT)
DECLARE @Country TABLE (ID INT, Name NVARCHAR(50), Code INT)

--INSERT Customer
INSERT INTO @Customer VALUES(1,'Test1',1)
INSERT INTO @Customer VALUES(2,'Test2',1)
INSERT INTO @Customer VALUES(3,'Test3',1)
INSERT INTO @Customer VALUES(4,'Test4',2)
INSERT INTO @Customer VALUES(5,'Test5',2)
INSERT INTO @Customer VALUES(6,'Test6',2)
INSERT INTO @Customer VALUES(7,'Test7',3)
INSERT INTO @Customer VALUES(8,'Test8',3)

--INSERT Country
INSERT INTO @Country VALUES(1,'UK',1)
INSERT INTO @Country VALUES(2,'ES',2)
INSERT INTO @Country VALUES(3,'MX',1)

--How many countries are received by means of a function Split
DECLARE @Count INT=(SELECT COUNT(*) FROM dbo.Split(@Countrys,','))

--We insert the result here
DECLARE @TableAux TABLE(CustomerID INT,CustomerName NVARCHAR(50),Country NVARCHAR(50))
--This is a Aux count
DECLARE @Help INT=1
--The Country Aux
DECLARE @CountryAux NVARCHAR(10)
WHILE @Help <=@Count
BEGIN
SELECT @CountryAux=(SELECT Data FROM dbo.Split(@Countrys,',') WHERE ID=@Help)

INSERT INTO @TableAux
SELECT TOP (@NumerOfCustomer)  CR.ID AS CustomerID,CR.[Name] AS CustomerName,CT.Name AS Country
FROM @Customer CR
INNER JOIN @Country CT ON CT.ID=CR.CountryID

--if you receive the string then use CT.Name
WHERE CT.Name=@CountryAux

SET @Help=@Help+1
--RESET 
SET @CountryAux=''
END


--Select ALL Result
SELECT * FROM @TableAux

SPLIT FUNCTION

CREATE FUNCTION [dbo].[Split]
(
@String NVARCHAR(4000),
@Delimiter NCHAR(1)
)
RETURNS TABLE 
AS
RETURN 
(
WITH Split(stpos,endpos) 
AS(
SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
UNION ALL
SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
FROM Split
WHERE endpos > 0
)
SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
FROM Split
)