I’m new to SQL & I’m trying to get the id for each category with the latest offer_valid_till. Below is my database structure.
| id | category_id | offer_valid_till |
|---|---|---|
| 1 | 1 | NULL |
| 2 | 2 | Sat, 23 Sep 2023 06:47:07 UTC +00:00 |
| 3 | 1 | Sun, 12 Jun 2022 09:55:53 UTC +00:00 |
| 4 | 3 | Tue, 12 Apr 2022 08:55:51 UTC +00:00 |
| 5 | 4 | NULL |
| 6 | 3 | Wed, 16 Nov 2021 03:18:10 UTC +00:00 |
Expected response:
| category_id | id |
|---|---|
| 1 | 3 |
| 2 | 2 |
| 3 | 4 |
| 4 | 5 |
Here is what I tried:
SELECT MAX(offer_valid_till), id
FROM OFFERS
GROUP BY category_id, id
HAVING MAX(offer_valid_till) = offer_valid_till
Any Rails or SQL solution will be a great help. Thanks!
>Solution :
Use this code :
select category_id,id from (
SELECT *
,ROW_NUMBER() over (partition by category_id order by offer_valid_till desc ) as rn
FROM OFFERS)a
where rn=1
This query set numbers the offer_valid_till field in order. Then it extracts the first value from each category.