Short description: I have a table with data that is updated over a certain period of time. Now the problem is that - depending on the nature of the sensor that sends the data - for this period of time there can be either 50 data sets or 50,000. Since I want to visualize this data (using ASP.NET/#), for For the first preview, I would like to SELECT only 1000 values from the table.
I already have an approach that does this: I am counting the strings for the time period of interest, with a simple “where” clause to indicate the identifier of the sensor, save it as a variable in SQL, and then split the counter () by 1000. I tried this in MS Access, where it works very well:
set @divider = select count(*) from table where [...] SELECT (Int([RowNumber]/@divider)), First(Value) FROM myTable GROUP BY (Int([RowNumber]/@divider));
The trick in Access was that I just have a data field ("RowNumber"), which is my PK / ID, and goes from 0 to. I tried to accomplish this in SQL Server using the ROW_NUMBER() method, which works more or less. I have the correct syntax for the method, but I cannot use the GROUP BY
Window functions can only be displayed in SELECT or ORDER BY clauses.
The value of ROW_NUMBER() cannot be in the GROUP BY statement.
Now I'm a little stuck. I tried to save the ROW_NUMBER value to a char or a separate column, and GROUP BY later, but I could not do it. And somehow I'm starting to think that my strategy may have its drawbacks ...?: /
To clarify again: I do not need SELECT TOP 1000 from my table, because it just means that I select the first 1000 values (depending on sorting). I need to SELECT each x-dimensional value, while I can calculate x (and I could even round it to INT if that helps to do this). I hope I was able to describe the problem understandable ...
This is my first post here on StackOverflow, I hope I haven’t forgotten anything significant or important, if you need more information (table structure, my queries so far ...), please feel free to ask, Any help or hint is very appreciated - thanks in advance! :)
Update: SOLUTION! Thanks a lot https://stackoverflow.com/users/52598/lieven !!!
Here's how I did it at the end:
I declare 2 variables - I am counting my lines and setting them to the first var. Then I use ROUND () for the newly assigned variable and dividing it by 1000 (because in the end I want values around 1000!). I divided this operation into 2 variables, because if I used the value from the COUNT function as the basis for my ROUND operation, there were some errors.
declare @myvar decimal(10,2) declare @myvar2 decimal(10,2) set @myvar = (select COUNT(*) from value_table where channelid=135 and myDate >= '2011-01-14 22:00:00.000' and myDate <= '2011-02-14 22:00:00.000' )
set @ myvar2 = ROUND (@ myvar / 1000, 0)
Now I have a rounded value that I want to be my step (take every x-dimensional value -> this is our "x";)) stored in @ myvar2. Then I will select the data of the desired time and channel and add ROW_NUMBER () as the column "rn" and finally add the WHERE clause to the outer SELECT, where I split ROW_NUMBER through @ myvar2 - when the module is 0, the row will be selected.
select * from ( select (ROW_NUMBER() over (order by id desc)) as rn, myValue, myDate from value_table where channel_id=135 and myDate >= '2011-01-14 22:00:00.000' and myDate<= '2011-02-14 22:00:00.000' ) d WHERE rn % @myvar2 = 0
It works like a charm - thanks again for https://stackoverflow.com/users/52598/lieven , see the comment below for the original post!