CODEWITHSUNDEEP
sqlsql-servert-sqldatetime
efuddy
efuddy

Reputation: 105

T SQL Select only first record for each day

I have created a view which includes specific tables from a SQL database and I can select all the samples from all the tables from a date range sucessfully. Here is a portion of the view design:

SELECT 'PLANT FLOW1' AS 'Tag', ts AS 'Timestamp', value AS 'Data'
FROM dbo.UASTP_150000_TL63
UNION
SELECT 'PLANT FLOW2' AS 'Tag', ts AS 'Timestamp', value AS 'Data'
FROM dbo.UASTP_150000_TL10
UNION
SELECT 'INFLUENT FLOW' AS 'Tag', ts AS 'Timestamp', value AS 'Data'
FROM dbo.UASTP_150000_TL1
UNION
SELECT 'EFFLUENT FLOW' AS 'Tag', ts AS 'Timestamp', value AS 'Data'
FROM dbo.UASTP_150000_TL2
UNION

I need to select only the first reading each day from each table. I found some sample code and tried to incorporate it, but it doesn't work.

Bad T-SQL code:

SELECT TOP (100) percent [Tag]
      ,[Timestamp]
      ,[Data]
  FROM [enteliwebDB].[dbo].[WIMS_View]
  where Timestamp >= DATEADD(day, -30, getdate())
  and Timestamp <= getdate()
  and where Tag in(
    SELECT min(Timestamp)
  )
  order by Tag, Timestamp

When I just run a query for a data range this is the kind of output I get which includes all samples for every table:

Tag Timestamp   Data
9012 TURBIDITY METER TREND  2019-03-10 11:41:14.0680000 5.1552605629
9012 TURBIDITY METER TREND  2019-03-10 11:51:14.0630000 5.14915704727
9012 TURBIDITY METER TREND  2019-03-10 12:01:14.0600000 5.12321662903
9012 TURBIDITY METER TREND  2019-03-10 12:11:14.0560000 5.16212701797
9012 TURBIDITY METER TREND  2019-03-10 12:21:14.0540000 5.12703132629
9012 TURBIDITY METER TREND  2019-03-10 12:31:14.0500000 5.13313484192
9012 TURBIDITY METER TREND  2019-03-10 12:41:14.0470000 5.17814922333
9012 TURBIDITY METER TREND  2019-03-10 12:51:14.0410000 5.14000177383

Can you tell me how to pull just the first sample each day from each table?

Upvotes: 2

Views: 5472

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522752

We can try using a CTE with your computed column to keep track of the table source:

WITH cte AS (
    SELECT 'PLANT FLOW1' AS tag, ts AS Timestamp, [value] AS data
    FROM dbo.UASTP_150000_TL63
    UNION ALL
    SELECT 'PLANT FLOW2', ts, [value] FROM dbo.UASTP_150000_TL10
    UNION ALL
    SELECT 'INFLUENT FLOW', ts, [value] FROM dbo.UASTP_150000_TL1
    UNION ALL
    SELECT 'EFFLUENT FLOW', ts, [value] FROM dbo.UASTP_150000_TL2
)

SELECT TOP 1 WITH TIES
    tag,
    ts,
    [value]
FROM cte
ORDER BY
    ROW_NUMBER() OVER (PARTITION BY tag, CONVERT(date, ts) ORDER BY ts);

Note carefully that the partition here is by tag (i.e. per table) and by the date of each record. Hence, we use CONVERT to obtain the date of each timestamp. We avoid a subquery altogether by using a TOP 1 WITH TIES trick where ROW_NUMBER appears in the ORDER BY clause.

Upvotes: 5

Michał Turczyn
Michał Turczyn

Reputation: 37500

Try this query. It uses subquery with ROW_NUMBER to determine first record per table ordered by timestamp:

SELECT [Tag],
       [Timestamp],
       [Data]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY ts DESC PARTITION BY Tag) [rn],
           [Tag],
           [Timestamp],
           [Data]
    FROM MyView
) [a] WHERE [rn] = 1;

Upvotes: 6

Related Questions