Geek asks and answers

Records of SQL queries in the range of boundaries and max / min out of range

I have three simple T-SQL queries. First you need to get records within the range (type DATETIME):

 SELECT value, timestamp 
 FROM myTable
 WHERE timestamp BETWEEN @startDT AND @endDT

the second is to get the closest record to @startDT (type DATETIME)

 SELECT TOP 1
   value, timestamp
 FROM myTable
 WHERE timestamp > @startDT
 ORDER BY timestamp DESC

and the last is to get the closest post after @endDT:

 SELECT TOP 1
   value, timestamp
 FROM myTable
 WHERE timestamp < @endDT
 ORDER BY timestamp ASC

I would like to get all records from three queries as one group of records. I tried to use UNION, but it seems that subqueries in UNION do not allow the ORDER BY clause. Is there an effective way to get my result?

 . .  * | * * * * * | * . . .
      start        end

The above chart just shows the * s entries as my required entries, and | ... | these are the boundaries.

By the way, the amount of data in myTable is huge. My understanding of UNION is not an efficient way to get data from UNION. Any efficient way to get data without UNION?

+3
5

, UNION.

MySQL (TESTED)

SELECT 
     dv1.timestamp, dv1.values
FROM 
     myTable AS dv1
WHERE 
    dv1.timestamp 
    BETWEEN (
           SELECT dv2.timestamp 
           FROM myTable AS dv2
           WHERE dv2.timestamp < '@START_DATE' 
           ORDER BY dv2.timestamp DESC 
           LIMIT 1
           )
    AND ( SELECT dv3.timestamp 
          FROM myTable AS dv3
          WHERE dv3.timestamp > '@END_DATE' 
          ORDER BY dv3.timestamp ASC 
          LIMIT 1
        )

EDIT , T-SQL.

T-SQL (NOT TESTED)

SELECT 
     dv1.timestamp, dv1.values
FROM 
     myTable AS dv1
WHERE 
    dv1.timestamp 
    BETWEEN (
           SELECT TOP 1 dv2.timestamp 
           FROM myTable AS dv2
           WHERE dv2.timestamp >  @START_DATE 
           ORDER BY dv2.timestamp DESC
           )
    AND ( SELECT TOP 1 dv3.timestamp 
          FROM myTable AS dv3
          WHERE dv3.timestamp <  @END_DATE 
          ORDER BY dv3.timestamp ASC
        )

. , ( ASC/DESC).

:)

+3

U max/min u need. Order by + top 1 , ur. n O (n 2), max O (n)

+1
SELECT value, timestamp 
 FROM myTable
 WHERE timestamp BETWEEN @startDT AND @endDT
union
select A.Value, A.TimeStamp
From (
 SELECT TOP 1
   value, timestamp
 FROM myTable
 WHERE timestamp > @startDT
 ORDER BY value, timestamp DESC ) A
Union
Select A.Value, A.TimeStamp
From (
 SELECT TOP 1
   value, timestamp
 FROM myTable
 WHERE timestamp < @endDT
 ORDER BY value, timestamp ASC ) A
+1

,

WHERE timestamp > @startDT


WHERE timestamp < @endDT

. ,

 . .  * | * * * * * | * . . .
      start        end

* s , |... | .

- .

So, following the descriptions and using the following mapping

myTable = Posts
value = score
timestamp  = creationdate

I wrote this request on data.stackexchange.com (modified from an exodream answer, but with comparison operators in correctud> reverse direction)

DECLARE @START_DATE datetime
DECLARE @END_DATE datetime
SET @START_DATE = '2010-10-20'
SET @END_DATE = '2010-11-01'

SELECT score, 
       creationdate 
FROM   posts 
WHERE  creationdate BETWEEN (SELECT TOP 1 creationdate 
                             FROM   posts 
                             WHERE  creationdate < @START_DATE 
                             ORDER  BY creationdate DESC) 
                             AND 
                                   (SELECT TOP 1 creationdate 
                                    FROM   posts 
                                    WHERE  creationdate > @END_DATE 
                                    ORDER  BY creationdate ASC) 
ORDER by creationDate

What are the exits

score creationdate        
----- ------------------- 
4     2010-10-19 23:55:48 
3     2010-10-20 2:24:50   
6     2010-10-20 2:55:54  
...
...
7     2010-10-31 23:14:48 
4     2010-10-31 23:18:17 
4     2010-10-31 23:18:48 
0     2010-11-01 3:59:38  

(382 row(s) affected)

Note that the first line and last lines are out of range

+1
source

You can put these ordered queries in subqueries so that they cannot connect directly to UNION. A bit annoying, but you get what you want.

SELECT value, timestamp 
 FROM myTable
 WHERE timestamp BETWEEN @startDT AND @endDT
 UNION
 SELECT value, timestamp 
 FROM (
 SELECT TOP 1
   value, timestamp
 FROM myTable
 WHERE timestamp > @startDT
 ORDER BY value, timestamp DESC 
 ) x
 UNION
 SELECT value, timestamp
 FROM (
 SELECT TOP 1
   value, timestamp
 FROM myTable
 WHERE timestamp < @endDT
 ORDER BY value, timestamp ASC
 ) x
0
source

More articles:


All Articles