Hi all,
I have some data in a table in duplicate with the values based on invoice numbers and in same date also all the values at the same time insert into the table.
Sample data in query
SELECT INV_SEQ_ID, RECORD_TYPE, INV_NO, CREATED_DATE
FROM
(
SELECT
1 AS inv_seq_id,
'CMHeader' AS record_type,
'CM1' AS inv_no,
TO_DATE('09/08/2023 10:08:42 AM', 'dd/mm/yyyy hh12:mi:ss am') AS created_date
FROM
dual
UNION ALL
SELECT
2 AS inv_seq_id,
'CMItem' AS record_type,
'CM1' AS inv_no,
TO_DATE('09/08/2023 10:08:42 AM', 'dd/mm/yyyy hh12:mi:ss am') AS created_date
FROM
dual
UNION ALL
SELECT
3 AS inv_seq_id,
'CMTax' AS record_type,
'CM1' AS inv_no,
TO_DATE('09/08/2023 10:08:42 AM', 'dd/mm/yyyy hh12:mi:ss am') AS created_date
FROM
dual
UNION ALL
SELECT
4 AS inv_seq_id,
'EndOfCM' AS record_type,
'CM1' AS inv_no,
TO_DATE('09/08/2023 10:08:42 AM', 'dd/mm/yyyy hh12:mi:ss am') AS created_date
FROM
dual
UNION ALL
SELECT
5 AS inv_seq_id,
'CMHeader' AS record_type,
'CM1' AS inv_no,
TO_DATE('09/08/2023 10:08:42 AM', 'dd/mm/yyyy hh12:mi:ss am') AS created_date
FROM
dual
UNION ALL
SELECT
6 AS inv_seq_id,
'CMItem' AS record_type,
'CM1' AS inv_no,
TO_DATE('09/08/2023 10:08:42 AM', 'dd/mm/yyyy hh12:mi:ss am') AS created_date
FROM
dual
UNION ALL
SELECT
7 AS inv_seq_id,
'CMTax' AS record_type,
'CM1' AS inv_no,
TO_DATE('09/08/2023 10:08:42 AM', 'dd/mm/yyyy hh12:mi:ss am') AS created_date
FROM
dual
UNION ALL
SELECT
8 AS inv_seq_id,
'EndOfCM' AS record_type,
'CM1' AS inv_no,
TO_DATE('09/08/2023 10:08:42 AM', 'dd/mm/yyyy hh12:mi:ss am') AS created_date
FROM
dual
UNION ALL
SELECT
9 AS inv_seq_id,
'CMHeader' AS record_type,
'CM1' AS inv_no,
TO_DATE('09/08/2023 10:08:42 AM', 'dd/mm/yyyy hh12:mi:ss am') AS created_date
FROM
dual
UNION ALL
SELECT
10 AS inv_seq_id,
'CMItem' AS record_type,
'CM1' AS inv_no,
TO_DATE('09/08/2023 10:08:42 AM', 'dd/mm/yyyy hh12:mi:ss am') AS created_date
FROM
dual
UNION ALL
SELECT
11 AS inv_seq_id,
'CMTax' AS record_type,
'CM1' AS inv_no,
TO_DATE('09/08/2023 10:08:42 AM', 'dd/mm/yyyy hh12:mi:ss am') AS created_date
FROM
dual
UNION ALL
SELECT
12 AS inv_seq_id,
'EndOfCM' AS record_type,
'CM1' AS inv_no,
TO_DATE('09/08/2023 10:08:42 AM', 'dd/mm/yyyy hh12:mi:ss am') AS created_date
FROM
dual
);
Query Output
INV_SEQ_ID RECORD_TYPE INV_NO CREATED_DATE
1 CMHeader CM1 09-AUG-23 10:08:42 AM
2 CMItem CM1 09-AUG-23 10:08:42 AM
3 CMTax CM1 09-AUG-23 10:08:42 AM
4 EndOfCM CM1 09-AUG-23 10:08:42 AM
5 CMHeader CM1 09-AUG-23 10:08:42 AM
6 CMItem CM1 09-AUG-23 10:08:42 AM
7 CMTax CM1 09-AUG-23 10:08:42 AM
8 EndOfCM CM1 09-AUG-23 10:08:42 AM
9 CMHeader CM1 09-AUG-23 10:08:42 AM
10 CMItem CM1 09-AUG-23 10:08:42 AM
11 CMTax CM1 09-AUG-23 10:08:42 AM
12 EndOfCM CM1 09-AUG-23 10:08:42 AM
Here in the data if we find that every record type start with CMHeader and ends with EndOfCM.
I want to take further the last set of records, so which are last set of records in this case the desired or last entered records are from the seq id 9 to 12. This is the INV_SEQ_ID primary key in the table.
Desired result output is
Regards,
Kalpataru