SQL query to select records with altering granularity

Question

I have a table of news articles, articles, each of which have a date attribute. Most days have multiple articles recorded, some days have none at all.

I'd like to be able to get a selection of articles with varying granularity - for instance, one per day, one per month, etc. I've found questions that deal with daily and even monthly, but as a user can select granularity (for instance, one article per 3 days), having multiple queries to deal with each possible level of granularity isn't feasible

Is this something possible using SQL, or will every article need to be selected, and then filtered through using a different language?

Maybe granularity is the wrong word - here's an example of the table:

id	date	headline
1	2020-01-01	This one weird trick...
2	2020-01-01	These two weird tricks...
3	2020-01-01	These fifty weird tricks...
4	2020-01-02	This one crazy trick...
5	2020-01-02	This one odd trick...
6	2020-01-03	These tricks...
7	2020-01-04	These tricks...
8	2020-01-05	These tricks...

With a granularity of one day, the query should return rows 1, 4, 6, 7, 8. With a granularity of 3 days, 1 and 7 will be picked, as 7 is the first record that's 3 days after the first.

Please can you provide some sample data and the results you want to achieve as it is very unclear, to me, what you are trying to achieve. — NickW, Feb 21 '21 at 18:52
@NickW I reread it and it was a bit vague - have edited in an example — Jacob Barrow, Feb 21 '21 at 19:30
(1) So you only want to return 1 record per date? If so, should it always be the record for that date with the lowest id or can it be any record? (2) You want to be able to pass into the query an integer x so that you can select a record every x days? (3) Is the query always going to start with the first (lowest id) record or do you want to be able to pass in a dynamic start date? — NickW, Feb 21 '21 at 20:49

score 1 · Accepted Answer · answered Feb 21 '21 at 20:27

You can use a recursive CTE that returns all the dates that you want to include in the results and join it to the table:

WITH cte(date) AS (
  SELECT MIN(date) FROM articles
  UNION ALL
  SELECT date(date, '+3 days')
  FROM cte
  WHERE date(date, '+3 days') <= (SELECT MAX(date) FROM articles)
)
SELECT MIN(a.id) id, a.date, a.headline 
FROM articles a INNER JOIN cte c
ON c.date = a.date
GROUP BY a.date

See the demo.

SQL query to select records with altering granularity

1 Answers1