How do I select rows that are not recent and are different from the last entry?-CodePudding

How do I select rows that are not recent and are different from the last entry? We recognize the differences by context field.

My example DB:

CREATE TABLE duel (
    id int,
    title varchar(255),
    PRIMARY KEY (id)
);

CREATE TABLE try (
    id int,
    duel_id int,
    context varchar(255),
    recent tinyint(1),
    PRIMARY KEY (id),
    FOREIGN KEY (duel_id) REFERENCES duel(id)
);

INSERT INTO duel (id,title) VALUES (1,"1"),(2,"2"),(3,"3"),(4,"4");
    
INSERT INTO try (id,duel_id,context,recent) VALUES
    (1,1,"a",0),(2,1,"a",0),(3,1,"a",1),(4,2,"a",0),(5,2,"b",0),
    (6,2,"b",1),(7,3,"a",0),(8,3,"a",0),(9,3,"b",1),(10,4,"c",0),
    (11,4,"a",0),(12,4,"c",1);

I would like to retrieve from try table rows with id: 4, 7, 8 and 11.

I tried the following:

SELECT * FROM try
WHERE recent != 1 AND (SELECT context FROM try WHERE recent = 1) != context;

But I have got the following error:

ERROR 1242 (21000) at line 120: Subquery returns more than 1 row

I don't know how to deal with it. Maybe there is a solution other than subqueries?

CodePudding user response：

Solution for your problem: (For MySQL 5.7)

SELECT id,duel_id,context,recent
FROM
(
SELECT *,
CASE 
WHEN @cntxt = context AND @did = duel_id AND recent = 0 THEN @cur
WHEN @cntxt = context AND @did = duel_id AND recent = 1 THEN (@cur := 1)
WHEN (@cntxt := context) IS NOT NULL AND (@did := duel_id) IS NOT NULL AND (@cur := recent) IS NOT NULL THEN recent
END as flag
FROM try, (SELECT @cur := 0, @cntxt := Null, @did := Null) r
ORDER BY duel_id, context,recent DESC
) as t
WHERE flag = 0
ORDER BY id;

db fiddle link

Solution for your problem: (For MySQL 8.0 )

WITH CT1 AS
(
SELECT *,
SUM(recent) OVER(PARTITION BY duel_id, context ORDER BY recent DESC) as rn
FROM try
)
SELECT id, duel_id, 
context, recent
FROM CT1
WHERE rn = 0
ORDER BY id;

dbfiddle link