Click here to Skip to main content
15,908,768 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello all, I'm bending my head over something that might have an obvious solution Im missing.
I have a big database in mssql and i'm using Grafana.
For retrieving a big piece of history I'd like to dynamically limit the returned results in my query.

What I have tried:

I have a log every second so I can get the total amount of rows by:

SQL
DECLARE @TotalRows REAL;
SET @TotalRows = DATEDIFF (SECOND, $__timeFrom(), $__timeTo())


Now I want to take every nth row to get a series of rough data.
the nth row is a percentage of the @TotalRows. Im my case I would like to calculate 1000000 (the max amount of rows) / @TotalRows

My query now looks like this

SQL
SELECT DateTime as time,
Value1 as "Value1",
Value2 as "Value2",
Value3 as "Value3"
FROM CKLog
WHERE DateTime > $__timeFrom() AND  DateTime < $__timeTo() 
ORDER BY DateTime ASC


I was looking into different approaches, but I can't really find a fitting one yet
Posted

Use dymamic sql queries - examples here Execute Dynamic SQL commands in SQL Server[^] or here - SQL Server Dynamic SQL[^]

Edit: Apologies for not reading your question properly
Quote:
I want to take every nth row to get a series of rough data.
You can do something like this
SQL
;;;WITH CTE AS
(
	select
	ROW_NUMBER() OVER (ORDER BY [DateTime]) as rn,
	Value1, Value2, Value3
	FROM CKLog
	WHERE DateTime > $__timeFrom() AND  DateTime < $__timeTo()
)
SELECT *	-- I'm being lazy here
FROM CTE
WHERE (rn % @n) = 0
order by rn;
Where @n is your "nth" calculation.

Incidentally using Value1 as "Value1", is pointless, just using Value1, will give exactly the same result. Also note the use of [ ] around the reserved word DateTime when it is used as a column name
 
Share this answer
 
v3
Comments
kenny_-_ 29-Dec-23 10:46am    
Now that is a great solution! I'm still learning in the SQL part, right now I have a solution that calculates the seconds an displays only the first couple of seconds of every minute. That was far from pretty, I like what you did I will test this soon. But I see that that will be a proper solution, thanks! And I know that "value1 as" is pointless, but it makes sense in the actual application, I just can't really post it as it is all closed source company stuff sadly.
Here is a comprehensive guide with multiple options for SQL Query with a Dynamic LIMIT:
https://copyprogramming.com/howto/how-to-write-a-sql-query-with-dynamic-limit[^]
 
Share this answer
 
Comments
CHill60 29-Dec-23 9:56am    
That link refers to LIMIT which is MySQL not MSSQL. Nor does it answer the OPs query - I made the same mistake initially, see my amended solution
M Imran Ansari 29-Dec-23 11:55am    
Oops! Thank you for the clarification.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900