CODEWITHSUNDEEP
sqlfirebirdfirebird-3.0
statistics pp
statistics pp

Reputation: 13

Distinct max values or only one maximum value from the serial number

I have a database (with two columns "id" and "serial") with serial numbers. For example:

id |Serial
10 | A18D003.000106
12 | A13D002.000100
122| A19D004.000107

There are many more numbers, but let's assume there are about 50 of each type of key:


From A18D003.00001 to 00052
From A13D002.00001 to 00053
From A19D004.00001 to 00057

I want to get the maximum value of the last 5 digits of the serial number. So the following should appear:

A18D003.00052
A13D002.00053
A19D004.00057

and get the absolute maximum value of what is after the dot:

A18D003.00052 -> 00052
A13D002.00053 -> 00053
A19D004.00057 -> 00057

It should only display:

A19D004.00057

How to do this in Firebird?

Upvotes: 0

Views: 367

Answers (3)

statistics pp
statistics pp

Reputation: 13

I tweaked your (Mark Rotteveel) statement a bit and I must say it works - I used RIGHT instead of substring

select val
from serial
order by RIGHT (serial, 5) desc
fetch first row only

Upvotes: 0

Arioch 'The
Arioch 'The

Reputation: 16065

Since you have task like that it seems that "Serial" is actualyl two fields, not one. And the table is NOT notmalized yet. So, you most probable have to normilize it.

See: https://en.wikipedia.org/wiki/Database_normalization

You would have first to add new VARCHAR columns (or maybe VARCHAR and INTEGER), SERIAL_PART_1 and SERIAL_PART_2

See Ch. 5.1.2. ALTER DATABASE

From that point you have two strategies.

If you can refactor all of your applications, the natural approach would be to remove the old "complex" column and to recreate it as an expression, based on the two "atom" columns. That way your applications would no be able to write into that "complex" column and whould be discouraged from using it in WHERE or GROUP or ORDER clauses (use new "atom" aolumns instead). Database-wise this seems a normal strategy, but can you effect all your applications in sync - i don't know.

If you can - you have to do a one-time data migration, and then re-work your applications. From now on they should only read the older, "compound" serial columns, never write into them and almost never filter by them.

select rdb$get_context('SYSTEM', 'ENGINE_VERSION') as version
     , rdb$character_set_name
from rdb$database;

VERSION | RDB$CHARACTER_SET_NAME                                                                                                      
:------ | :---------------------------------------------------------------------------------------------------------------------------
3.0.5   | UTF8                                                                                                                        

create table serial (
  val varchar(50) not null
)

insert into serial (val) values ('A18D003.00052');

insert into serial (val) values ('A13D002.00053');

insert into serial (val) values ('A19D004.00057');

select val
from serial
order by substring(val from 9) desc
fetch first row only

| VAL           |
| :------------ |
| A19D004.00057 |

insert into serial (val) values ('A19F104.00057');

insert into serial (val) values ('A18D003.00010');

insert into serial (val) values ('A13D002.0008');

insert into serial (val) values ('A19D004.00000025');

--  https://stackoverflow.com/questions/71555536   alternative answer

alter table serial
  add serial_part_1 varchar(8) not null
 ,add serial_part_2 integer not null
 ,add serial_part_2_text COMPUTED BY (lpad(serial_part_2, 6, '0'))

-- make sure lpad (6, xxx ) always work properly
alter table serial
  add check ((serial_part_2 >= 0) and (serial_part_2 < 1000000))

-- STRATEGY database-first, one-time data migration, needs refactoring of all applications

-- should this be just a non-unique index (allowing rows repetitions)
-- or should that be a PRIMARY KEY instead ???

create index idx_serial_by_parts on serial(serial_part_1,serial_part_2)

create index idx_serial_by_part2 on serial(serial_part_2)

-- https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref25/firebird-25-language-reference.html#fblangref25-functions-string

update serial set
  serial_part_1 = LEFT(val, POSITION( '.' IN val) - 1)
 ,serial_part_2 = SUBSTRING(val FROM POSITION( '.' IN val) + 1)



7 rows affected










select * from serial



VAL              | SERIAL_PART_1 | SERIAL_PART_2 | SERIAL_PART_2_TEXT
:--------------- | :------------ | ------------: | :-----------------
A18D003.00052    | A18D003       |            52 | 000052            
A13D002.00053    | A13D002       |            53 | 000053            
A19D004.00057    | A19D004       |            57 | 000057            
A19F104.00057    | A19F104       |            57 | 000057            
A18D003.00010    | A18D003       |            10 | 000010            
A13D002.0008     | A13D002       |             8 | 000008            
A19D004.00000025 | A19D004       |            25 | 000025            








alter table serial 
  drop val








alter table serial
  add val COMPUTED BY ( serial_part_1 || '.' || serial_part_2_text  )








select * from serial



SERIAL_PART_1 | SERIAL_PART_2 | SERIAL_PART_2_TEXT | VAL           
:------------ | ------------: | :----------------- | :-------------
A18D003       |            52 | 000052             | A18D003.000052
A13D002       |            53 | 000053             | A13D002.000053
A19D004       |            57 | 000057             | A19D004.000057
A19F104       |            57 | 000057             | A19F104.000057
A18D003       |            10 | 000010             | A18D003.000010
A13D002       |             8 | 000008             | A13D002.000008
A19D004       |            25 | 000025             | A19D004.000025








select SERIAL_PART_1, Max(SERIAL_PART_2), Max(val)
from serial
group by SERIAL_PART_1



SERIAL_PART_1 | MAX | MAX           
:------------ | --: | :-------------
A13D002       |  53 | A13D002.000053
A18D003       |  52 | A18D003.000052
A19D004       |  57 | A19D004.000057
A19F104       |  57 | A19F104.000057








with mx as ( select Max(SERIAL_PART_2) as mx2 from serial )
select * from serial, mx 
  where SERIAL_PART_2 = mx.mx2



SERIAL_PART_1 | SERIAL_PART_2 | SERIAL_PART_2_TEXT | VAL            | MX2
:------------ | ------------: | :----------------- | :------------- | --:
A19D004       |            57 | 000057             | A19D004.000057 |  57
A19F104       |            57 | 000057             | A19F104.000057 |  57





db<>fiddle here




Alternative strategy would be to keep BOTH new and old columns, and use a smart trigger to keep synchronizing those data values in both directions.


That would let you keep old applications, yet would require a very careful online data conversion-synchronization programming.


For example, in the sample below i did not think much about different combinations of NULL and not-NULL data values. To use this approach in production it would be your task to check for all possible data value sets, correct and broken, and to arm your code against any bad combination.





select rdb$get_context('SYSTEM', 'ENGINE_VERSION') as version
     , rdb$character_set_name
from rdb$database;



VERSION | RDB$CHARACTER_SET_NAME                                                                                                      
:------ | :---------------------------------------------------------------------------------------------------------------------------
3.0.5   | UTF8                                                                                                                        








create table serial (
  val varchar(50) not null
)








insert into serial (val) values ('A18D003.00052');








insert into serial (val) values ('A13D002.00053');








insert into serial (val) values ('A19D004.00057');








select val
from serial
order by substring(val from 9) desc
fetch first row only



| VAL           |
| :------------ |
| A19D004.00057 |








insert into serial (val) values ('A19F104.00057');








insert into serial (val) values ('A18D003.00010');








insert into serial (val) values ('A13D002.0008');








insert into serial (val) values ('A19D004.00000025');








--  https://stackoverflow.com/questions/71555536   alternative answer

alter table serial
  add serial_part_1 varchar(8) not null
 ,add serial_part_2 integer not null
 ,add serial_part_2_text COMPUTED BY (lpad(serial_part_2, 6, '0'))








-- make sure lpad (6, xxx ) always work properly
alter table serial
  add check ((serial_part_2 >= 0) and (serial_part_2 < 1000000))








-- STRATEGY applications-first, on-change two-ways data migration, 
-- hopefully can avoid immediate refactoring of all applications

-- should this be just a non-unique index (allowing rows repetitions)
-- or should that be a PRIMARY KEY instead ???

create index idx_serial_by_parts on serial(serial_part_1,serial_part_2)








create index idx_serial_by_part2 on serial(serial_part_2)








-- https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref25/firebird-25-language-reference.html#fblangref25-ddl-trgr-crtalter
-- https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref25/firebird-25-language-reference.html#fblangref25-psql-triggercreate

CREATE TRIGGER serial_data_synchronize
  ACTIVE BEFORE INSERT OR UPDATE 
  ON serial
AS
BEGIN
  -- the next condition might need defensive programming
  -- what should be  TRUE OR FALSE OR NULL ?
  IF ( (NEW.val IS NULL) 
       OR (NEW.serial_part_1 <> OLD.serial_part_1)
       OR (NEW.serial_part_2 <> OLD.serial_part_2)
  ) THEN BEGIN
    NEW.val = NEW.serial_part_1 || '.' || NEW.serial_part_2_text;
    EXIT;
  END

  IF ( (NEW.val IS DISTINCT FROM OLD.val)
       OR ( NEW.serial_part_1 IS NULL ) 
       OR ( NEW.serial_part_2 IS NULL ) 
  ) THEN BEGIN
    NEW.serial_part_1 = LEFT(NEW.val, POSITION( '.' IN NEW.val) - 1);  
    NEW.serial_part_2 = SUBSTRING(NEW.val FROM POSITION( '.' IN NEW.val) + 1);
  END
END








select * from serial



VAL              | SERIAL_PART_1 | SERIAL_PART_2 | SERIAL_PART_2_TEXT
:--------------- | :------------ | ------------: | :-----------------
A18D003.00052    |               |             0 | null              
A13D002.00053    |               |             0 | null              
A19D004.00057    |               |             0 | null              
A19F104.00057    |               |             0 | null              
A18D003.00010    |               |             0 | null              
A13D002.0008     |               |             0 | null              
A19D004.00000025 |               |             0 | null              








update serial set val = val; -- trigger the trigger





7 rows affected










select * from serial



VAL              | SERIAL_PART_1 | SERIAL_PART_2 | SERIAL_PART_2_TEXT
:--------------- | :------------ | ------------: | :-----------------
A18D003.00052    | A18D003       |            52 | 000052            
A13D002.00053    | A13D002       |            53 | 000053            
A19D004.00057    | A19D004       |            57 | 000057            
A19F104.00057    | A19F104       |            57 | 000057            
A18D003.00010    | A18D003       |            10 | 000010            
A13D002.0008     | A13D002       |             8 | 000008            
A19D004.00000025 | A19D004       |            25 | 000025            








insert into serial (val) values ('A19F104.00042');








insert into serial (serial_part_1,serial_part_2) values ('A19F104', 21);








select * from serial



VAL              | SERIAL_PART_1 | SERIAL_PART_2 | SERIAL_PART_2_TEXT
:--------------- | :------------ | ------------: | :-----------------
A18D003.00052    | A18D003       |            52 | 000052            
A13D002.00053    | A13D002       |            53 | 000053            
A19D004.00057    | A19D004       |            57 | 000057            
A19F104.00057    | A19F104       |            57 | 000057            
A18D003.00010    | A18D003       |            10 | 000010            
A13D002.0008     | A13D002       |             8 | 000008            
A19D004.00000025 | A19D004       |            25 | 000025            
A19F104.00042    | A19F104       |            42 | 000042            
A19F104.000021   | A19F104       |            21 | 000021            








update serial 
   set serial_part_2 = 12
 where val = 'A19F104.00042'








update serial 
   set val = 'A18D003.00022'
 where serial_part_2 = 10








select * from serial



VAL              | SERIAL_PART_1 | SERIAL_PART_2 | SERIAL_PART_2_TEXT
:--------------- | :------------ | ------------: | :-----------------
A18D003.00052    | A18D003       |            52 | 000052            
A13D002.00053    | A13D002       |            53 | 000053            
A19D004.00057    | A19D004       |            57 | 000057            
A19F104.00057    | A19F104       |            57 | 000057            
A18D003.00022    | A18D003       |            22 | 000022            
A13D002.0008     | A13D002       |             8 | 000008            
A19D004.00000025 | A19D004       |            25 | 000025            
A19F104.000012   | A19F104       |            12 | 000012            
A19F104.000021   | A19F104       |            21 | 000021            








select SERIAL_PART_1, Max(SERIAL_PART_2), Max(val), LIST(val, ', ')
from serial
group by SERIAL_PART_1



SERIAL_PART_1 | MAX | MAX           | LIST                                         
:------------ | --: | :------------ | :--------------------------------------------
A13D002       |  53 | A13D002.0008  | A13D002.0008, A13D002.00053                  
A18D003       |  52 | A18D003.00052 | A18D003.00022, A18D003.00052                 
A19D004       |  57 | A19D004.00057 | A19D004.00000025, A19D004.00057              
A19F104       |  57 | A19F104.00057 | A19F104.000012, A19F104.000021, A19F104.00057








with mx as ( select Max(SERIAL_PART_2) as mx2 from serial )
select serial.* from serial, mx 
  where SERIAL_PART_2 = mx.mx2



VAL           | SERIAL_PART_1 | SERIAL_PART_2 | SERIAL_PART_2_TEXT
:------------ | :------------ | ------------: | :-----------------
A19D004.00057 | A19D004       |            57 | 000057            
A19F104.00057 | A19F104       |            57 | 000057            





db<>fiddle here


Upvotes: 1
Mark Rotteveel
Mark Rotteveel
Reputation: 109136
You can use substring to extract the last part of the serial, order by it descending, and then only fetch the first row:


select val
from serial
order by substring(val from 9) desc
fetch first row only



Fiddle: https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=213309564fcb403103763e02bcd03311


This example assumes that the serial always has the separating dot in position 8, otherwise you may need to use the regular expression form of substring (e.g. substring(val similar '%.#"%#"' escape '#')), or use position.


Given the complication mentioned in the comments of having serials with 5 or 6 digits, you can also try using one of the following approaches:


    

  1. Trim leading zeroes and cast to integer:
    

    cast(trim(leading '0' from substring(val from 9)) as integer)

    

    2. 

  2. As a variant of the previous, use substring to extract the number:
    

    cast(substring(val similar '%.0*#"([1-9][0-9]*)|0#"' escape '#') as integer)

    

    3. 

  3. Left pad (lpad) zeroes to a common length (for simplicity, I'm assuming 6 here):
    

    lpad(substring(val from 9), 6, '0')

    

    4. 



Upvotes: 1
Related Questions