0

My database table looks like this:

id articleId createDate modifiedDate content
1 145 01.01.2021 01.01.2021 blabla
2 145 01.01.2021 02.01.2021 blabla
3 145 01.01.2021 03.01.2021 blabla
4 146 02.10.2021 02.10.2021 blabla
5 147 05.04.2021 05.04.2021 blabla
6 147 05.04.2021 07.04.2021 blabla

So if a content is updated and saved again, then it is saved again in DB with modified date and same articleId. What I'm try to get is all content (latest ,not repeated) which are containing word "blabla".

I have written this query

SELECT * FROM db where content like '%blabla%' group by articleId

but this didnt work. Do you have any idea?

lemon
  • 14,875
  • 6
  • 18
  • 38
kontorrr testor
  • 51
  • 1
  • 4

2 Answers2

0

Create your custom order using ROW_NUMBER() and CTE :

WITH Ordered_db AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY articleId ORDER BY modifiedDate DESC) AS RowNumber
    FROM db
    WHERE content LIKE '%blabla%'
)
SELECT *
FROM Ordered_db
WHERE Ordered_db.RowNumber = 1
T N
  • 4,322
  • 1
  • 5
  • 18
Meyssam Toluie
  • 1,061
  • 7
  • 21
  • 3
    *If* this is the answer, I have lost count of the amount of times this answer has been posted, and the question should just be closed as a duplicate of one of the canonical duplicates (such as [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group)). I see no reason for this answer to be posted for about the 10,000th time (I don't feel I am understating that guesstimate either). At the moment, it is unclear what the OP is *actually* asking. – Thom A Oct 17 '22 at 11:07
0

I believe something like this can work. Recalling from my memory.
MAX() can be used on sql date type.
Query searches for max date for every article with "some" contents.

SELECT *
FROM table t1
WHERE t1.modifiedDate = (
  SELECT MAX(modifiedDate) 
  FROM table
  WHERE articleId = t1.articleId
  AND content LIKE CONCAT("%", "search_value or bind", "%")
);
Flewz
  • 343
  • 9