Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table in which every record has an ID, a date and a value. There are a lot of records, and I want to get those dates and values, to create a graph. But I do not want to have more than 100 points.

What I need to do, is if for example there are 300 records, one for every day, to execute a query which will take 100 records, but uniformly. I mean if I have for example

23, 1 January 2014, 5
34, 2 January 2014, 54
45, 3 January 2014, 35
56, 4 January 2014, 55
67, 5 January 2014, 35
78, 6 January 2014, 56
89, 7 January 2014, 53
90, 8 January 2014, 55
94, 9 January 2014, 57

For example, If I wanted 3 results maximum, my query should return for example the record for 1 January 2014, 4 January 2014 and 7 January 2014. How is it possible to do that? I cannot think anything...

share|improve this question
2  
Your example is 5+55+53 = 113. More than 100. Either I've misunderstood, or you've explained badly. Even how the results are picked isn't explained. Why would you skip 2nd and 3rd Jan? The requirements make zero sense –  Phil Jan 23 at 19:47
2  
Also, which RDBMS? –  Phil Jan 23 at 19:59

1 Answer 1

up vote 1 down vote accepted

You could assign a row number to your results, then show every Xth record from that result. You do this by calculating the total number of records, and dividing that by your desired number of intervals. If the modulo is 0, spit out that record.

In my example below, I want to 10 records (approx) from the sysobjects table, sorted alphabetically by name. The result is 10 records.

This was a lot harder to explain than to write.

;with SummaryTable as
(
select row_number() over (order by name) as RowNum,
       name,
       (select count(*) from sys.objects) as 'TotalCount'
 from sys.objects
)
select *
  from SummaryTable 
 where RowNum % (TotalCount / 10)  = 0
share|improve this answer
1  
Thanks a lot datagod –  Jim Blum Jan 24 at 9:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.