0

Background: A SQL-Server database table is used to store weekly reports of some system memory usage, meaning that each week a new table row/record/line is inserted.

The question is how a fixed number (e.g. 5 records) of records can be queried, with the side-condition that those records are equally spaced within the time-range.

if this was the table

id | memory
----------- 
01  1231
02  1241
03  1260
04  1300
[.... other 92 rows ...]
97  2001
98  2002
99  2020

and with said 5 records I would like have a table as such

id | memory
----------- 
01  1231
25  1600
50  1800
75  1900
99  2020

since it would most represent the overall picture of the data.

5
  • You can get a rowcount of the target table and then divide that number by your "fixed number" and get your divisor. Then select your target table where the ID value modulus your divisor is 0 (ID%@divisor).
    – Queue Mann
    Jan 2, 2020 at 15:06
  • @QueueMann Do you think that this would work in query?
    – fraleone
    Jan 2, 2020 at 15:15
  • you mean without having to query the rowcount ahead of time?
    – Queue Mann
    Jan 2, 2020 at 15:18
  • @QueueMann I mean that the result would be a sql query, implying I need not use any other artifice like a programming language python, java....
    – fraleone
    Jan 2, 2020 at 15:20
  • yes - you can do the entire thing using only SQL.
    – Queue Mann
    Jan 2, 2020 at 15:23

2 Answers 2

1

There's probably a more elegant way to achieve this but it can be done by using the NTILE function inside a CTE.

WITH cte AS (

  SELECT NTILE(5) OVER (ORDER BY id asc) AS dist,
  memory
  FROM TABLE)

  SELECT TOP 1 id, memory FROM cte WHERE dist = 1

  UNION

   SELECT TOP 1 id, memory FROM cte WHERE dist = 2

  UNION

   SELECT TOP 1 id, memory FROM cte WHERE dist = 3

  UNION

   SELECT TOP 1 id, memory FROM cte WHERE dist = 4

  UNION

   SELECT TOP 1 id, memory FROM cte WHERE dist = 5;
0
declare @row_count int;
declare @rows_returned int = 5;

select @row_count = count(*) from MyTable;
select * from MyTable where id%(@row_count/@rows_returned) = 0;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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