SELECT any FROM system

Question

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.

Rebozo

It's not clear what you're asking. What's the problem you're having? Are you asking if you can query all tables in all databases or are you asking if you can query a table named "system"? The second example should work fine. The first example won't work like you think, those are two pieces of text, not dates. You'll need to store dates in a proper format. – Territoriality

I think they mean simple queries to get lists of dates or numbers between given ranges, on the fly. – Obedient

@Tom: I assume he's asking for generate_series. – Slayton

What dbms are you using? – Kym

Why are you doing this in the DB? This seems like something that the logic layer should be doing. – Prognostication

@Bryan Ross, I've needed to do something along these lines when passing dates into a database function and simple BETWEEN date1 AND date2 wouldn't work. – Aeromedical

Answer 1

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.

Slayton

ah. you beat me to it... very nice answer +1 – Lockhart

MYSQL: SELECT num from (select @num:=@num+1 as num from big_table, (select @num:=10) num) as q WHERE num<=19 (https://mcmap.net/q/499228/-generate_series-equivalent-in-mysql) – Necessitous

@JakubKania: you need to have big_table first. – Slayton

@Slayton you need to have any table with at least two rows and you can cross join from there. Having big_table is not a problem, the problem is that you may need another yet another cross join if the recordset is too big. – Necessitous

@JakubKania: you may not have write access to the database as it commonly happens with report developers. You can get away with (SELECT 1 UNION ALL SELECT 2 …) q in this case, but the exact query layout would depend on the number of records you want. I'm pretty aware of the workarounds, the question is not about them. – Slayton

Answer 2

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

Harm

Answer 3

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
)

Lockhart

Answer 4

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.

Dreyer

I would rather use MVJ's DATE FUNCTION it is much more flexible and the performance is fantastic. sqlteam.com/forums/topic.asp?TOPIC_ID=61519 – Harm

That is not available in MySQL nor in Postgresql and SQLite – Dreyer

Answer 5

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

Marcos

Answer 6

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.

Kym

Answer 7

In SQL Server you can use the BETWEEN keyword.

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

Selfheal

Can this be used other than in a where clause? Or, can a where clause be used other than selecting from a table? – Kym

Just need to remember that BETWEEN is inclusive of the bounds while the OP's example excludes them. – Schumann

Answer 8

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

Pyrexia

You can. But AFAIK, WHERE must be used when selecting from a table or view. The question seems to be asking how to generate dates or numbers, ie NOT from a table or view that already is populated. – Kym