Extract specific row - SelectCommand

I have 5 rows of data coming into my database every 2 hours from a GSM modem and I require only the last (latest) row of data as thats the only relevant one but need all of the data for a chart...

Is there a way of using something to only recall one row after every fourth.


Example.

1pm 23, 25, 20,99,21

3pm 34, 56, 55, 65, 30

only showing 21 and 30 in data.

Answers


With CTE:

WITH NumberedRows AS
(
    SELECT Serial, DeviceLevel, ROW_NUMBER() OVER (ORDER BY DateReceived DESC) AS RowNumber
    FROM TBLReadings 
) 
SELECT *
FROM NumberedRows
WHERE RowNumber % 5 = 0

Use the following SQL statement to fetch the required data:

select top (1) from your table order by DateTimeField Desc

It's not so many information, need more for the exact solution.

But if the timestamp of the records differ and you already can select the 5 for 1pm and 3pm, then you can try something similar in the where clause:

timestamp = select max(timestamp) from (the_5_recs_for_the_given_intervall)

Hope you understand what I want to say!


Need Your Help

SSAS Calculated Member - how to do percent of total based on another measure

ssas mdx business-intelligence

I am currently trying to create a calculated measure for an SSAS 2008 R2 cube. In a financial cube dealing with accounts receivable data, I have a "Gross Balance" measure, and a "Days Since DOS" me...

How to get generic (polymorphic) lambda in scala?

scala haskell polymorphism type-inference

Update (2018): my prayers were answered in Dotty (Type Lambdas), so the following Q&A is more "Scalac"-related