How to get max(column_name) from a table irrespective of a where clause in the query?

Question

I have a requirement of inserting data into a table and one of the columns is seq_number. I have a where clause in the select query and I want to insert the max(seq_num)+1 for every record that I want to insert. The max of seq_num is giving me the max for the where clause and not the actual max from the table.

INSERT INTO TABLE_NAME 
SELECT
    NEWID(),
    MAX(SEQ_ORD_R)+1,  -- This should be the max from table irrespective of the where clause
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP
FROM TABLE_NAME
WHERE SOFA_K = 'FD5B6BE8-F1CF-42C0-9216-B13163413F96'

You do this by defining the `seq_ord_r` to be an auto-increment column and simply not doing this. — Gordon Linoff, Nov 25 '20 at 13:57
Look here: https://stackoverflow.com/questions/65001331/sql-insert-into-table-new-rows-foreach-field-in-same-table/65003720#65003720 It can help you — Slava Rozhnev, Nov 25 '20 at 14:03
MySql has no function `NEWID` to my knowledge (is this a user-defined function?) and is the column name `seq_num` per your description or `SEQ_ORD_R`per your SQL? See [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query). — Booboo, Nov 25 '20 at 14:17
Gordon Linoff -- Thanks for quick response, I don't know if they will agree in changing the data type at this point but I will ask. Slava Rozhnev -- Thanks, I am looking into the link Booboo -- sql server has NEWID() function to create a UUID (Unique Identifier). The problem column here is the SEQ_ORD_R which I have referenced as seq_num in the description above. — shaik rehan, Nov 25 '20 at 14:25

Booboo · Answer 1 · 2020-11-25T14:48:08.063

0

SELECT MAX(SEQ_ORD_R) FROM TABLE_NAME INTO @K;
INSERT INTO TABLE_NAME 
SELECT
    NEWID(),
    (SELECT @K := @K+1),
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP
FROM TABLE_NAME
WHERE SOFA_K = 'FD5B6BE8-F1CF-42C0-9216-B13163413F96'

edited Nov 25 '20 at 14:48

answered Nov 25 '20 at 14:30

Booboo

38,656
3
37
60

Inserting multiple rows. Already tried this, all rows give same SEQ_ORD_R – shaik rehan Nov 25 '20 at 14:31
This is why you should create a Minimal Reproducible Example. That is to say, go to a site like db-fiddle.com and create sample tables with enough data that demonstrate the problem and against which folks on this site can test their supposed solutions. It saves everybody (well, not you, but us) a lot of time. – Booboo Nov 25 '20 at 14:35
Thanks.. But I was able to solve the issue. This is my first post so didn't know about db-fiddle.com. I will keep that in mind. I will post the answer shortly – shaik rehan Nov 25 '20 at 16:57

score 0 · Answer 2 · edited Nov 25 '20 at 18:01

0

INSERT INTO TABLE_NAME 
SELECT
    NEWID(),
    max_seq_r+row_number() over (partition by 1 order by max_seq_r),  -- This should be the max from table irrespective of the where clause
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP
FROM TABLE_NAME A inner join 
(select max(SEQ_ORD_R) as max_seq_r from TABLE_NAME) B on 1=1
WHERE SOFA_K = 'xyz'

edited Nov 25 '20 at 18:01

Booboo

38,656
3
37
60

answered Nov 25 '20 at 17:10

shaik rehan

1

Just for your information: This solution requires MySql 8+ (I made no such assumption with my answer.) and as with my answer you will need to use function `UUID`. – Booboo Nov 25 '20 at 18:07

How to get max(column_name) from a table irrespective of a where clause in the query?

2 Answers2