CODEWITHSUNDEEP
sqlansi-sql
strike_noir
strike_noir

Reputation: 4174

SELECT any FROM system

Can any of these queries be done in SQL?

SELECT dates FROM system 
WHERE dates > 'January 5, 2010' AND dates < 'January 30, 2010'

SELECT number FROM system 
WHERE number > 10 AND number < 20

I'd like to create a generate_series, and that's why I'm asking.

Upvotes: 6

Views: 2580

Answers (8)

Thomas
Thomas

Reputation: 64645

The simplest solution to this problem is a Tally or Numbers table. That is a table that simply stores a sequence of integers and/or dates

Create Table dbo.Tally ( 
                        NumericValue int not null Primary Key Clustered
                        , DateValue datetime NOT NULL 
                        , Constraint UK_Tally_DateValue Unique ( DateValue )
                        )
GO

;With TallyItems
As (
    Select 0 As Num
    Union All
    Select ROW_NUMBER() OVER ( Order By C1.object_id ) As Num
    From sys.columns as c1
        cross join sys.columns as c2
    )
Insert dbo.Tally(NumericValue, DateValue)
Select Num, DateAdd(d, Num, '19000101')
From TallyItems 
Where Num

Once you have that table populated, you never need touch it unless you want to expand it. I combined the dates and numbers into a single table but if you needed more numbers than dates, then you could break it into two tables. In addition, I arbitrarily filled the table with 100K rows but you could obviously add more. Every day between 1900-01-01 to 9999-12-31 takes about 434K rows. You probably won't need that many but even if you did, the storage is tiny.

Regardless, this is a common technique to solving many gaps and sequences problems. For example, your original queries all ran in less than tenth of a second. You can also use this sort of table to solve gaps problems like:

Select NumericValue
From dbo.Tally
    Left Join MyTable
        On Tally.NumericValue = MyTable.IdentityColumn
Where Tally.NumericValue Between SomeLowValue And SomeHighValue

Upvotes: 0

Pentium10
Pentium10

Reputation: 207952

If you want to get the list of days, with a SQL like

select ... as days where date is between '2010-01-20' and '2010-01-24'

And return data like:

days 
---------- 
2010-01-20
2010-01-21
2010-01-22
2010-01-23
2010-01-24

This solution uses no loops, procedures, or temp tables. The subquery generates dates for the last thousand days, and could be extended to go as far back or forward as you wish.

select a.Date 
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.Date between '2010-01-20' and '2010-01-24'

Output:

Date
----------
2010-01-24
2010-01-23
2010-01-22
2010-01-21
2010-01-20

Notes on Performance

Testing it out here, the performance is surprisingly good: the above query takes 0.0009 sec.

If we extend the subquery to generate approx. 100,000 numbers (and thus about 274 years worth of dates), it runs in 0.0458 sec.

Incidentally, this is a very portable technique that works with most databases with minor adjustments.

Upvotes: 1

EvilTeach
EvilTeach

Reputation: 28837

In Oracle

WITH
START_DATE AS
(
    SELECT TO_CHAR(TO_DATE('JANUARY 5 2010','MONTH DD YYYY'),'J') 
    JULIAN FROM DUAL
),
END_DATE AS
(
    SELECT TO_CHAR(TO_DATE('JANUARY 30 2010','MONTH DD YYYY'),'J') 
    JULIAN FROM DUAL
),
DAYS AS
(
    SELECT END_DATE.JULIAN - START_DATE.JULIAN DIFF
    FROM START_DATE, END_DATE
)
SELECT  TO_CHAR(TO_DATE(N + START_DATE.JULIAN, 'J'), 'MONTH DD YYYY') 
        DESIRED_DATES
FROM 
START_DATE,
(
    SELECT LEVEL N 
    FROM DUAL, DAYS
    CONNECT BY LEVEL < DAYS.DIFF
)

Upvotes: 1

Eric
Eric

Reputation: 29

You can select a range by using WHERE and AND WHERE. I can't speak to performance, but its possible.

Upvotes: 0

ZippyV
ZippyV

Reputation: 13028

In SQL Server you can use the BETWEEN keyword.

Link: http://msdn.microsoft.com/nl-be/library/ms187922(en-us).aspx

Upvotes: 0

David Oneill
David Oneill

Reputation: 13075

Not sure if this is what you're asking, but if you are wanting to select something not from a table, you can use 'DUAL'

select 1, 2, 3 from dual;

will return a row with 3 columns, contain those three digits.

Selecting from dual is useful for running functions. A function can be run with manual input instead of selecting something else into it. For example:

select some_func('First Parameter', 'Second parameter') from dual;

will return the results of some_func.

Upvotes: 0

JonH
JonH

Reputation: 33153

Sort of for dates... Michael Valentine Jones from SQL Team has an AWESOME date function

Check it out here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425471

I assume you want to generate a recordset of arbitrary number of values, based on the first and last value in the series.

In PostgreSQL:

SELECT  num
FROM    generate_series (11, 19) num

In SQL Server:

WITH    q (num) AS
        (
        SELECT  11
        UNION ALL
        SELECT  num + 1
        FROM    q
        WHERE   num < 19
        )
SELECT  num
FROM    q
OPTION (MAXRECURSION 0)

In Oracle:

SELECT  level + 10 AS num
FROM    dual
CONNECT BY
        level < 10

In MySQL:

Sorry.

Upvotes: 12

Related Questions