how to find start and last from sequence value sql-CodePudding

Given the following input data:

id	seq	date	Serial Num	wh	amount	supp
557	79264887	2023-07-25	1000990036512	82	14209	1709
557	79264888	2023-07-25	1000990036513	82	14209	1709
557	79264889	2023-07-25	1000990036514	82	14209	1709
557	79264890	2023-07-25	1000990036515	82	14209	1709
557	79264891	2023-07-25	1000990036516	82	14209	1709
557	79264892	2023-07-25	1000990036517	82	14209	1709
557	79264893	2023-07-25	1000990036518	82	14209	1709

How can I make the output to look like as follows?

id	date	start serial	end serial	wh	amount	supp
557	2023-07-25	1000990036512	1000990036518	82	14209	1709

CodePudding user response：

You can try with:

select id, date, wh, amount, supp, min(serial_num) as start_serial, 
       max(serial_num) as end_serial
  from mytable
 group by id, date, wh, amount, supp
                
   ID DATE               WH     AMOUNT       SUPP START_SERIAL    END_SERIAL
----- ---------- ---------- ---------- ---------- --------------- ---------------
  557 2023-07-25         82      14209       1709 1000990036512   1000990036518

Thank you

CodePudding user response：

In T-SQL, there is this analytic function called FIRST_VALUE & LAST_VALUE, you can use these functions to get the first and last values. The SQL code should look something like this.

SELECT id, date,
    FIRST_VALUE(N'Serial Num') OVER (
        PARTITION BY id ORDER BY seq
        ) AS N'start serial',
    LAST_VALUE(N'Serial Num') OVER (
        PARTITION BY id ORDER BY seq
        ) AS N'end serial',
    wh,
    amount,
    supp
FROM your_table 
WHERE ID = 557 AND WH = 82;