Tuesday, July 9, 2013

In SQL Query, Only Return Every n-th Record

A little SQL trick that helps in some cases. In my case, I wanted to select some data that is logged into the table every several seconds, and then quickly plot it in Excel, but over a date range of a few months. So, I run a query

SELECT "timestamp", valueiwant
FROM mytable
order by timestamp

And that potentially leaves me with thousands or hundreds of thousands of rows. However, to visualise a trend over time, I don't need to plot each and every value on the graph. I'll be happy with 1/10 or even 1/100 of records. Here is how I can use ROW_NUMBER to achieve that.

 SELECT "timestamp", instrumenttimestamp, ROW_NUMBER() OVER (order by timestamp) AS rownum
    FROM mytable
 order by timestamp
) AS t
WHERE t.rownum % 25 = 0

Row number returns the sequential number of a row in the result set. The WHERE clause then checks if the number is a multiple of 25, therefore only rows 25, 50, 75, 100 etc. will be returned by the outer query.


Return row of every n'th record
by . Also posted on my website

No comments: