CODEWITHSUNDEEP
sqlsql-server
Mutex
Mutex

Reputation: 446

MS SQL how to select n rows from table even if table has n - x rows

I have a table with a following data:

ID | Name
---------
1  | John
2  | Alice

And when I want to select 5 rows I want to get the next data:

ID | Name
---------
1  | John
2  | Alice
1  | John
2  | Alice
1  | John

Is there any ideas how to make this?

Upvotes: 2

Views: 156

Answers (2)

Pradeep Kumar
Pradeep Kumar

Reputation: 6969

-- to create a sample table.
CREATE TABLE table1 (ID BIGINT, NAME VARCHAR(255))
INSERT INTO table1 VALUES (1, 'John'), (2, 'Alice')

-- set number of rows you need
DECLARE @RowsReqd INT = 5  

-- find out the max ID we want to generate 
DECLARE @Limit INT
SELECT @Limit = MAX(ID) FROM table1

-- generate the list
;WITH NumbersList
AS (
    SELECT 1 AS Number, 1 AS ID
    UNION ALL
    SELECT Number + 1, Number % @Limit + 1 FROM NumbersList
    WHERE Number < @RowsReqd    
    )
SELECT T.*
FROM NumbersList NL
INNER JOIN table1 T ON T.ID = NL.ID
ORDER BY NL.Number
OPTION (MAXRECURSION 10000) -- increase this value to generate more numbers

OUTPUT:

ID   NAME
1    John
2    Alice
1    John
2    Alice
1    John

Upvotes: 2

gofr1
gofr1

Reputation: 15977

Works for n > 1 rows:

;WITH cte AS (
SELECT *
FROM (VALUES
(1, 'John'),
(2, 'Alice')
) AS t(ID, Name)
)
,res AS (
    SELECT  id,
            name,
            ROW_NUMBER() OVER (partition by id ORDER BY ID) as pid
    FROM cte
    UNION ALL
    SELECT  c.id,
            c.name,
            pid + 1
    FROM res r
    INNER JOIN cte c 
        ON pid = c.id 
)

SELECT TOP 5 
        id,
        name
FROM res

Output:

id          name
----------- -----
1           John
2           Alice
1           John
2           Alice
1           John

(5 row(s) affected)

Upvotes: 2

Related Questions