How to select only last X values where column value changed

user_10569948 0
Viewed 83

How can I select (MySQL) last for example 5 rows where value in NOTE field has changed, by every distinct ID?

Example:

    CREATE TABLE `LOGTABLE` (
  `ID` bigint(20) NOT NULL,
  `ITEM_ID` int(11) NOT NULL,
  `DATETIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `TYPE` tinyint(4) NOT NULL,
  `NOTE` varchar(200) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

    ALTER TABLE `LOGTABLE`
  MODIFY `ID` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
COMMIT;

    INSERT INTO `LOGTABLE` (`ID`, `ITEM_ID`, `DATETIME`, `TYPE`, `NOTE`) VALUES
(2, 167, '2021-06-15 16:47:23', 0, 'T'),
(3, 168, '2021-06-15 16:48:11', 0, 'F'),
(4, 167, '2021-06-15 16:59:51', 0, 'T'),
(5, 168, '2021-06-15 17:00:48', 0, 'F'),
(6, 167, '2021-06-15 17:12:19', 0, 'T'),
(7, 168, '2021-06-15 17:13:25', 0, 'T'),
(8, 167, '2021-06-15 17:24:47', 0, 'F'),
(9, 168, '2021-06-15 17:26:02', 0, 'F'),
(10, 167, '2021-06-15 17:37:15', 0, 'T');

And I want to have this result:

167- IDs 2,8,10
168- IDs 3,7,9
mysql sql
2 Answers

You can use Lag function and Cte table as follows:

WITH Cte AS (
    SELECT *,
           LAG(Note,1) OVER(PARTITION BY Item_ID ORDER BY Id) PrevNote
    FROM [dbo].[LOGTABLE]
),   
Final AS(
    SELECT *,
           RANK() OVER (PARTITION BY ITem_ID ORDER BY ID DESC) rnk 
    FROM Cte AS c
    WHERE Note <> ISNULL(PrevNote,'')
)
SELECT * 
FROM final
WHERE rnk <= 5
ORDER BY ITem_ID

It gives you result below:

ID  ITEM_I  DATETIME    TYPE      NOTE  PrevNote    rnk
10  167 2021-06-15 17:37:15.000 0   T   F            1
8   167 2021-06-15 17:24:47.000 0   F   T            2
2   167 2021-06-15 16:47:23.000 0   T   NULL         3
9   168 2021-06-15 17:26:02.000 0   F   T            1
7   168 2021-06-15 17:13:25.000 0   T   F            2
3   168 2021-06-15 16:48:11.000 0   F   NULL         3

It returns Maximum 5 rows for each Item_Id That has changed

user_54808870

here is one way:

select item_id, GROUP_CONCAT(ID) Ids
from (
select *, case when NOTE <> LAG(NOTE,1,'X') over (partition by item_id order by datetime) then 1 else 0 end lnote
from LOGTABLE
) t
where lnote = 1 
group by item_id

item_id | Ids   
------: | :-----
    167 | 2,8,10
    168 | 3,7,9

db<>fiddle here

user_13674540
Related