0

I want the date_end column to iteratively compare the next row and delete it until the date_end is greater than in it. e.g

 R|      date_end
 1|       1993-12-20
 2|       1993-11-17
 3|  1993-11-17
 4|   1993-12-30
 5|   1993-12-30
 6|   1994-01-21  
 7|   1993-01-05

I want to store Row 1, 4, 6 in a new temporary table. I have used the self join technique unsuccessfully. There are other column in the table as well.

SELECT 
    a.*,
    b.datestart AS datestart2,
    b.hourtimestart AS hourtimestart2,
    b.dateend AS dateend2,
    b.hourtimeend AS hourtimeend2,
    b.Amount AS Amount2,
    b.mintime AS mintime2
FROM
    tempdb.combined3 a,
    tempdb.combined4 b;

SELECT 
    *
FROM
    tempdb.combined5
WHERE
    (dateend < datestart2)
ORDER BY datestart , datestart2;

enter image description here

UPDATE: https://www.db-fiddle.com/f/6JCMtaMJvrq9D6vZZFu8xt/2

Improve this question
7
  • If 'it' is 1, then I don't understand why '1' would be included in this result Commented Apr 17, 2020 at 7:38
  • @strawberry I am not sure what you mean by both of your comments. I have added a fiddle, though. Commented Apr 17, 2020 at 8:29
  • We have differing interpretations of the term 'minimal'!! Commented Apr 17, 2020 at 8:31
  • I have provided plenty data so that it is easy to answer and easier to understand for other community members later on. @strawberry Commented Apr 17, 2020 at 8:35
  • Other community members like minimal, complete, reproducible examples too. Commented Apr 17, 2020 at 8:44

2 Answers 2

Reset to default
1

I cursor would do that for you. I'm no MySQL expert but I think this would work. It basically converts a select into a loop in which you may interact with the variables.

Please check that code sample:

CREATE PROCEDURE curdemo()
BEGIN

  DECLARE current datetime2;
  DECLARE previous datetime2;

  DECLARE cur1 CURSOR FOR 
      SELECT b.dateend AS dateend2
        FROM tempdb.combined4 b;

  OPEN cur1;

  read_loop: LOOP
    FETCH cur1 INTO current;

    IF done THEN
      LEAVE read_loop;
    END IF;    

    IF current > previous THEN
      /* insert into your table here */
      SET previous = current;
    END IF;

  END LOOP;

  CLOSE cur1;
END;

You may check more about cursors here: https://dev.mysql.com/doc/refman/8.0/en/cursors.html

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

@SaadAmir provide me with a sample data so I may test that on my end. Can you post the create table with some data sample so I may test that? indeed I was not able to test that script since I have no data like that on my end.
1

EDITED:

Try this query

SELECT *,
       CASE WHEN A.DateEnd > B.DateEnd THEN 1 ELSE 0 END AS checking
 FROM combined4 A LEFT JOIN
(SELECT r-1 AS BR,R,DateEnd FROM combined4) B
ON A.R=BR
HAVING checking=1;
Improve this answer

6 Comments

I added R column but it is still not working. The following rows appeared. 1 7 9 11 13 14 18 and so on
can you update the fiddle with your original table structure and data? you can insert data where R from 1 to 20. Copy your own original query into the fiddle as well. Once you've done that, please click "Update" and copy the fiddle link into your question. Thanks
I have done as you said with 200 rows. Here is the link. db-fiddle.com/f/6JCMtaMJvrq9D6vZZFu8xt/2 @tcadidot0
@SaadAmir , I think I made a mistake,how did I get the correct value in the first place? Could you please check the updated query in my answer, does it return correctly? Thanks
It is not updated. Can you please provide fiddle link? @tcadidot0
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.