>> I have a table with customer ids and different something_stages a customer can be in starting with A, B, C and D each starting at different dates. <<
Then we need some constraints to enforce this business rule don't we?
>> Now a customer can be in multiple something_stages and I want to group them as below example (column rownum) <<
Since tables have no ordering by definition, what do you mean by "grouping" them? It looks like you want a three-part primary key for the table. It also sounds like a customer might be in a stage for a duration, so we would need start and stop timestamps for that duration. Your sample data doesn't support this
I'm afraid your sample data is a mess. We have no constraints, no key, and the datatypes don't make any sense. I'm also trying to figure out exactly what a row number is in terms of a logical data model; it sounds like a physical description of the storage used for the table on some hard disk on some machine.
Identifiers should never be numeric because we don't do calculations on them. They use what is called a nominal scale. I'm also curious why you are using the old Sybase style table constructor instead of the ANSI standard that Microsoft is had for quite a few years now.
CREATE TABLE Foobar --- needs a real name
(customer_id CHAR(10) NOT NULL, -- nominal scale
something_stage_name CHAR(1) NOT NULL
CHECK(something_stage_name IN ('A', 'B', 'C', 'D')),
something_stage_attempt_nbr INTEGER NOT NULL
CHECK(something_stage_attempt_nbr > 0),
something_start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
--- something_end_date DATE,
PRIMARY KEY (customer_id, something_stage_name, something_stage_attempt_nbr)));
INSERT INTO Foobar
('0001', 'A', '2017-01-01', 1),
('0001', 'A', '2018-01-01', 2),
('0001', 'B', '2018-02-01', 2),
('0001', 'C', '2019-01-01', 2),
('0002', 'A ', '2017-01-01', 1),
('0002', 'B', '2017-02-01', 1),
('0003', 'A', '2017-01-01', 1),
('0003', 'B', '2017-02-01', 1),
('0003', 'A', '2018-01-01', 2),
('0003', 'B', '2018-02-01', 2),
('0003', 'C', '2018-03-01', 2);
This sounds like you might want to progress the customer from stage A, to B, to C and finish at D. Is it also true that the only attempt that matters is the last one? You might want to look at
Please post DDL and follow ANSI/ISO standards when asking for help.