carl Brooks carl Brooks - 5 months ago 15
SQL Question

How to retreive max value between two numbers and iterate through

I have a number table that contains only a NumberID with values 1 all the way to 22.

I want to perform a CROSS JOIN on the number table to itself so that it iterates through between numbers 0 (which is not in the Number table and 5). This should output all possible scores.

E.g

0-0
0-1
0-2
0-3
0-4
0-5
1-0
1-1
1-2
1-3
1-4
1-5
2-0


etc.

What do I need to do for this to work?

INSERT INTO dbo.Score(Result)
SELECT cast(n.NumberID AS VARCHAR (10)) + ' - ' + cast(nn.NumberID AS VARCHAR (10)) AS Result
FROM dbo.Number n
CROSS JOIN dbo.Number nn
ORDER BY n.NumberID, nn.NumberID

Answer

You can select numbers 1 to 6 and subtract 1 to achieve 0 to 5.

INSERT INTO dbo.Score(Result)
SELECT cast(n.NumberID-1 AS VARCHAR (10)) + ' - ' + cast(nn.NumberID-1 AS VARCHAR (10)) AS Result
FROM dbo.Number n
CROSS JOIN dbo.Number nn
WHERE n.NumberID <= 6 AND nn.NumberID <= 6
ORDER BY n.NumberID, nn.NumberID