My database table looks like this:

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?

CodePudding user response：

Create your custom order using ROW_NUMBER() and CTE :

WITH Orderd_db AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY articleId ORDER BY modifiedDate DESC) AS RowNumber
FROM db
WHERE content LIKE '%blabla%'
)
SELECT * FROM Orderd_db WHERE Orderd_db.RowNumber = 1

CodePudding user response：

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", "%")
);