maintain last record

Shambhu Rai 1,411

HI Expert, needs deleted records as mentioned below 'd' means deleted ... 'I' means inserted we need to delete the the record --- 1st inserted --- 2nd deleted same record so output is mentioned

Create table table1  (col1 varchar(20),col2 varchar(20),col3 

varchar(20),col4 varchar(20)) 
Create table table2 (col1 varchar(20),col2 varchar(20),col3 varchar(20),col4 varchar(20))
 insert into table1 values('I','1','2','3','4') 
insert into table1 values('D','1','2','3','4')   
insert into table1 values('D','4','5','6','7') 
insert into table1 values('u','8','9','10','12') 
insert into table1 values('D','8','9','10','12') 
insert into table1 values('I','11','12','13','14') 

EXPECTED OUTPUT
('D','1','2','3','4') 
('D','4','5','6','7') 
('I','11','12','13','14')

Erland Sommarskog 107.1K Reputation points

2024-02-10T10:43:07.4466667+00:00

It is great that you posted CREATE TABLE + INSERT, but there is only data for one of the tables? And code does not run; I get Column name or number of supplied values does not match table definition. I recommend that you test your code before you post it.

Also, I am completely in the dark with understanding you want these precise rows. You title talks about maintaining the last row. If so, you need to tell us what column(s) that defines the order of the rows.

Shambhu Rai 1,411

FYI>> when col2 , col3 col4 col5 values are same the last record should be updated ... in this case needs update statement to get expected values

Create table default.table16 (col1 char(20),col2 char(20),col3 char(20),col4 char(20),col5 char(20));
 insert into table16 values('I','1','2','3','4') ;

insert into table16 values('D','1','2','3','4')  ; 

insert into table16 values('D','4','5','6','7') ;

insert into table16 values('u','8','9','10','12') ;

insert into table16 values('D','8','9','10','12') ;

EXPECTED OUTPUT ('D','1','2','3','4') ('D','4','5','6','7') ('I','11','12','13','14')

Erland Sommarskog 107.1K Reputation points

2024-02-10T11:58:31.27+00:00

What defines what is the "last record"? Keep in mind that tables are unordered sets. That you seem to insert the rows in a certain order is irrelevant, as long as the order is not encoded in the data.
Shambhu Rai 1,411 Reputation points

2024-02-10T12:06:56.23+00:00

that means for remain 4 columns 2nd insert statement is updating col1 vlaues so col2 to 4 are primary key values
Shambhu Rai 1,411 Reputation points

2024-02-10T12:27:47.0633333+00:00

col2, col3,col4 col5 are primary key columns based on col1 should uudate

Erland Sommarskog 107.1K

That means for remain 4 columns 2nd insert statement is updating col1 vlaues so col2 to 4 are primary key values

Again, tables are unordered sets, so if this is all you data you have, the problem is not solvable. That is, if there are some sort of order between the rows, this needs to be encoded into the table. Here is an example:

Create table table16 (rowno int PRIMARY KEY, col1 char(20),col2 char(20),col3 char(20),col4 char(20),col5 char(20));
 insert into table16 values(1, 'I','1','2','3','4') ;
insert into table16 values(2, 'D','1','2','3','4')  ; 
insert into table16 values(3, 'D','4','5','6','7') ;
insert into table16 values(4, 'u','8','9','10','12') ;
insert into table16 values(5, 'D','8','9','10','12') ;
go
; WITH numbering AS (
   SELECT *, rownoingrp = row_number() OVER(PARTITION BY col2, col3, col4, col5 ORDER BY rowno DESC)
   FROM   table16
)
SELECT col1, col2, col3, col4, col5 
FROM   numbering
WHERE  rownoingrp = 1
go
DROP TABLE table16

Shambhu Rai 1,411 Reputation points

2024-02-10T15:01:04.2333333+00:00

when i added new row it is not working insert into table16 values(5, 'U','13','91','14','98');
Shambhu Rai 1,411 Reputation points

2024-02-10T15:05:51.71+00:00

EXPECTED OUTPUT ('D','1','2','3','4') ('D','4','5','6','7') ('I','11','12','13','14')
Erland Sommarskog 107.1K Reputation points

2024-02-10T15:35:52.84+00:00

So that row with U should not be returned. Can you explain why?

Shambhu Rai 1,411

use this insert statement
and below is new row so it should be exist as there is not delete statement after that ('I','11','12','13','14')

 insert into table1 values('I','1','2','3','4') 
insert into table1 values('D','1','2','3','4')   
insert into table1 values('D','4','5','6','7') 
insert into table1 values('u','8','9','10','12') 
insert into table1 values('D','8','9','10','12') 
insert into table1 values('I','11','12','13','14')

Shambhu Rai 1,411 Reputation points

2024-02-10T15:44:51.3633333+00:00

EXPECTED OUTPUT ('D','1','2','3','4') ('D','4','5','6','7') ('I','11','12','13','14')
Erland Sommarskog 107.1K Reputation points

2024-02-10T16:36:44.38+00:00

Why should the row with ('D','8','9','10','12') not be included in the output?

This discussion should be a lot more efficient if you explained what you want to achieve from start to end. Bear in mind that we have no knowledge about your actual problem, and we are not able to read your mind.
Shambhu Rai 1,411 Reputation points

2024-02-10T21:36:17.49+00:00

Very simple Col 2 to 5 is primary key if new value came for col1 then it should take latest record or same record should come use last insert statement and output
Erland Sommarskog 107.1K Reputation points

2024-02-10T21:56:35.9766667+00:00

Primary key value? Why are there duplicates in those rows in that case? Oh, you mean that they are primary keys in another table?

It may be "very simple" to you, but we don't know what you are working with, so we are very much in the dark.
Shambhu Rai 1,411 Reputation points

2024-02-11T01:12:34.37+00:00

Suggestion pls
Shambhu Rai 1,411 Reputation points

2024-02-11T03:10:10.04+00:00

sir when new records enter then it should update the records based on primary keys col2 to col4 ... please do not ans if you do not understand
Shambhu Rai 1,411 Reputation points

2024-02-12T00:31:49.7533333+00:00

Your expected output does not contain any record with PK value (8,9,10,12). What do you want to happen to those records? its new record and should insert the data... it is insert update condition
LiHongMSFT-4306 25,651 Reputation points

2024-02-16T01:52:25.9933333+00:00

Hi @Shambhu Rai

that means for remain 4 columns 2nd insert statement is updating col1 vlaues so col2 to 4 are primary key values> col2, col3,col4 col5 are primary key columns based on col1 should uudate

It is really hard to understand what you mean.

please do not ans if you do not understand

How could we help you if we do not understand your issue? You help yourself a lot if you post a clear and concise question.

Note that to make the best use of Microsoft Q&A, please avoid duplicate questions.
Shambhu Rai 1,411 Reputation points

2024-02-16T02:00:16.87+00:00

the below mentioned thread is block because of above there.... both are different thread and example even if code structure used same. Please read the thread carefully before blocking https://learn.microsoft.com/en-us/answers/questions/1532886/sql-delete-and-update
info 0 Reputation points

2024-02-16T14:23:36.2033333+00:00
Hi Shambhu, May i assume that you want to be able to select the 'latest' combination of Col2,Col3,Col4,Col5?

To do so you need a common 'logic' to determine which record would be always the 'last' one, nd then create the query upon that logic.

The sample in the other thread mentioned also contains a datetime column which would make it a lot easier to select the latest row of the combination.

If there is no date/time available it would be difficult to determine which record is the 'last' one. In that case you would have to determine whether there a order can be determined in the I,D,U label.

Based upon the example including date i have the following suggestion:

First make a pre-selection which adds a row number for the combinations of Col2,Col3,Col4,Col5

The rownumber will be ordered in descending(desc) order so the 'latest' date will always get number 1. If only one date exists it will also be 1

The next step is to select the table with only the latest record =1 keeping only the latest records. Example:

with cte_prepare as ( select * , row_number () over (partition by Col1, Col2, Col3, Col4 order by Date_enter DESC) as LatestRecord from table1 ) Select * from cte_prepare where LatestRecord =1

Does this provide a solution?

If not i would like to know what the criteria is to select the 'correct' records.

Kind Regards,

Share via

maintain last record