-1

I have a Database that has product names in column 1 and product release dates in column 2. I want to find 'old' products by their release date. However, I'm only interested in finding 'old' products that released a minimum of 1 year ago. I cannot make any edits to the original database infrastructure.

The table looks like this:

Product|   Release_Day
   A   |   2018-08-23
   A   |   2017-08-23
   A   |   2019-08-21
   B   |   2018-08-22
   B   |   2016-08-22
   B   |   2017-08-22
   C   |   2018-10-25
   C   |   2016-10-25
   C   |   2019-08-19

I have already tried multiple versions of DISTINCT, MAX, BETWEEN, >, <, etc.

SELECT DISTINCT product,MAX(release_day) as most_recent_release
FROM Product_Release
WHERE 
release_day between '2015-08-22' and '2018-08-22' 
and release_day not between '2018-08-23' and '2019-08-22'
GROUP BY 1
ORDER BY MAX(release_day) DESC

The expected results should not contain any products found by this query:

SELECT DISTINCT product,MAX(release_day) as most_recent_release
FROM Product_Release
WHERE 
release_day between '2018-08-23' and '2019-08-22'
AND product = A
GROUP BY 1

However, every check I complete returns a product from this date range.

This is the output of the initial query:

Product|Most_Recent_Release
   A   |   2018-08-23
   B   |   2018-08-22
   C   |   2015-10-25

And, for example, if I run the check query on Product A, I get this:

Product|Most_Recent_Release
   A   |   2019-08-21
John Miller
  • 1
  • 2

1 Answers1

1

Use HAVING to filter on most_recent_release

SELECT product, MAX(release_day) as most_recent_release
FROM Product_Release
GROUP BY product
HAVING most_recent_release < '2018-08-23'
ORDER BY most_recent_release DESC

There's no need to use DISTINCT when you use GROUP BY -- you can't get duplicates if there's only one row per product.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • the platform I am using will not allow me to use DATE_SUB. Is this because the datatype in column 2 is BOOLEAN? Would an appropriate substitute look like this? ```HAVING MAX(release_day) < '2018-08-22'```? – John Miller Aug 22 '19 at 19:35
  • I got rid of `DATE_SUB`. How can `most_recent_release` be boolean? – Barmar Aug 22 '19 at 19:37
  • When I tried the other answer using SUM, it returned an error message that said "unknown: SUM(Boolean)". – John Miller Aug 22 '19 at 19:38