Update minimum value based on consecutive values using SQL query

user_528912 0
Viewed 49

Trying to update the minimum CardSerial into the column MinimumCardSerial. Below is my table structure.

CardID      CardSerial       Denomination Minimumcardserial
----------- ---------------  ------------ -----------------
2685680     214026002        10           
2685681     214026003        10           
2685682     214026004        10           
2685778     214026100        10           
2685779     214026101        10           
2685780     214026102        10           
2685878     214026200        10           
2685879     214026201        10           
2685880     214026202        10

Expective below result

CardID      CardSerial       Minimumcardserial
----------- ---------------- ----------- ------------ -------------
2685680     214026002        214026002
2685681     214026003        214026002
2685682     214026004        214026002
2685778     214026100        214026100
2685779     214026101        214026100
2685780     214026102        214026100
2685878     214026200        214026200
2685879     214026201        214026200
2685880     214026202        214026200

The below query useful to find the minimum cardserial based on consecutive group, however trying to find a update query to update the minimum cardserial into the source table itself. Can some one help in this please.

WITH
  sequenced AS 
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY Denomination ORDER BY cardSerial) AS sequence_id,
    *   FROM  Cards )

SELECT
  min(cardSerial),  Denomination
FROM   Sequenced
GROUP BY
  Denomination, 
  cardSerial - sequence_id
ORDER BY
  Denomination, 
  cardSerial - sequence_id
tsql sql-server sql
1 Answers

This is a type of gaps and islands problem. Assuming that cardserial is a number, you can subtract an enumerated value -- and that difference is constant for incrementing values.

Then just take the minimum over that grouping:

select c.*,
       min(cardserial) over (partition by cardserial - seqnum) as minimum_cardserial
from (select c.*,
             row_number() over (order by cardserial) as seqnum
      from cards c
     ) c;

If cardserial is not a number, you can just convert to numeric:

min(cardserial) over (partition by convert(numeric(38), cardserial)) - seqnum)
user_11440350
Related