Limit MSSQL results dynamically

Question

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 28-Dec-23 22:24pm

kenny_-_

Add a Solution

2 solutions

Solution 2

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[^]

Posted 29-Dec-23 2:45am

M Imran Ansari

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.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Accepted Answer · 2023-12-28T22:28:00

~~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