CODEWITHSUNDEEP
sqlsql-serverselect
user1319951
user1319951

Reputation: 505

A query to SELECT a number range

I am having the following problem.

I would like to select a currency value from a database which will act as a default value on the top result of the query (this part is already done and is not a part of my main problem).

I want to use a query that kind of looks like this:

SELECT valkurs, valkurs 'vk'
FROM xx
WHERE valkod='EUR' AND foretagkod=300
UNION
--(My problem is that i can't find out what to write here)

My problem is that I would like to attach a range of values from 1.0 to 20.0 with 0.1 in incremental steps to the original query mentioned above.

An example output can look like this:

8.88, 8.88

1.0, 1.0

1.1, 1.1

1.2, 1.2

...

20.0, 20.0

Is it possible anyhow?

Due to implementation issues this has to be done in a query...

Upvotes: 1

Views: 10394

Answers (2)

BlackTigerX
BlackTigerX

Reputation: 6146

Old, but I think some people will benefit from my answer, which is a much better implementation than the accepted answer

WITH e1(n) AS
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 10
e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b), -- 10*10
e3(n) AS (SELECT 1 FROM e1 CROSS JOIN e2), -- 10*100
numbers as (SELECT n = ROW_NUMBER() OVER (ORDER BY n)/10.0
FROM e3)
select n, n from numbers
where n between 1 and 20

Upvotes: 2

GarethD
GarethD

Reputation: 69759

You can use the system table Master..spt_values to generate a sequential list:

SELECT  Number = CAST(1 + (Number / 10.0) AS DECIMAL(4, 1)),
        Number2 = CAST(1 + (Number / 10.0) AS DECIMAL(4, 1))
FROM    Master..spt_values
WHERE   Type = 'P'
AND     Number BETWEEN 0 AND 200

So to combine in the correct order with your current query I would use:

SELECT  valkurs, VK = valkurs
FROM    (   SELECT  valkurs, SortOrder = 0
            FROM    xx
            WHERE   valkod = 'EUR' 
            AND     foretagkod = 300
            UNION ALL
            SELECT  valkurs = CAST(1 + (Number / 10.0) AS DECIMAL(4, 1)), SortOrder = 1
            FROM    Master..spt_values
            WHERE   Type = 'P'
            AND     Number BETWEEN 0 AND 190
        ) T
ORDER BY T.SortOrder, t.valkurs;

ADDENDUM

There are some that do not advocate the use of Master..spt_values due to the fact that it is not documented, so it could be removed from future versions of sql-server. If this is a major concern you can use ROW_NUMBER() to generate a sequential list (using any table with enough rows as the source, I have gone for sys.all_objects):

SELECT  valkurs, VK = valkurs, 
FROM    (   SELECT  valkurs, SortOrder = 0
            FROM    xx
            WHERE   valkod = 'EUR' 
            AND     foretagkod = 300
            UNION ALL
            SELECT  TOP 191 
                    valkurs = 1 + ((ROW_NUMBER() OVER(ORDER BY object_id) - 1) / 10.0),
                    SortOrder = 1
            FROM    sys.all_objects
        ) T
ORDER BY T.SortOrder, t.valkurs;

Upvotes: 4

Related Questions