February 18, 2011 at 7:41 am
Hi
I am new to SQL and I am just wondering if there is a solution to a problem I am having.
I am using the piece of code below.
What I am doing is selecting a field from a table and ordering that field in descending order. Using the Row_Number feature I then specify which row, R, I want to return.
I calculate R seperately using another piece of code. R=(count(field1)/100)-1 for any given day. Essentially I count the entries in field1 every day. I perform this operation every day. The only fields that change every day are the dates and the value of R. I am then returned a single row of data.
My problem is I have to often populate large historical tables with this data. I can only run the code once for each day and for each value of R.
Is there anyway I can alter this code such that it can return multiple values of field1 over several dates?
The only way I can think of is to repeat the code multiple times using UNION but I am hoping there is a more efficient way.
Thanks
da_shee
SELECT *
FROM (SELECT Field1,
ROW_NUMBER() OVER (ORDER BY field1 desc ) R
FROM table
WHERE date >= TO_DATE ('20110215', 'YYYYMMDD')
AND date < TO_DATE ('20110216', 'YYYYMMDD')
)
WHERE R = 1227
--Note: 1227 = (count(field1)/100)-1 --calculated each day using seperate code
February 18, 2011 at 8:21 am
eoghanmaths
To help those who wish to help you, please, please post the table definition, some sample data and required results, following the samples and methods given by the article whose link is the first link in my signature block.
Another question is "TO_DATE" is not a standard function in T-SQL, so posting that code may also be of assistance.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply