How to make a "group by ordered" ?

[Lilian BRUN]

I have a table State_history :
Id State DateModif object_id
1 A 10/10/01 1
2 B 10/11/01 1
3 B 11/11/01 1
4 B 11/12/01 1
5 C 01/01/02 1
6 C 01/02/02 1
7 A 02/03/02 1
The challenge is to select only differents states of an object

I try this :
Select max(DateModif),id,state,object_id
from State_history
group by id,state,object_id;

I get :
Id State DateModif object_id
4 B 11/12/01 1
6 C 01/02/02 1
7 A 02/03/02 1

I wanted :
Id State DateModif object_id
1 A 10/10/01 1
4 B 11/12/01 1
6 C 01/02/02 1
7 A 02/03/02 1

The problem is that it doesnt select the first row, which is a different
state from the line 7, because the object take different state between line
1 and 7

Any idea to make this query ?
Regards,

Lilian.

[Lilian BRUN]

If you want to try :

CREATE TABLE TEST
(
ID NUMBER primary key,
ETAT VARCHAR2(1),
DATE_CREAT DATE
);

INSERT INTO TEST ( ID, ETAT, DATE_CREAT ) VALUES (
1, 'A', TO_Date( '10/10/2001 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TEST ( ID, ETAT, DATE_CREAT ) VALUES (
2, 'B', TO_Date( '10/11/2001 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TEST ( ID, ETAT, DATE_CREAT ) VALUES (
3, 'B', TO_Date( '10/12/2001 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TEST ( ID, ETAT, DATE_CREAT ) VALUES (
4, 'C', TO_Date( '11/12/2001 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TEST ( ID, ETAT, DATE_CREAT ) VALUES (
5, 'C', TO_Date( '12/10/2001 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TEST ( ID, ETAT, DATE_CREAT ) VALUES (
6, 'C', TO_Date( '12/13/2001 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TEST ( ID, ETAT, DATE_CREAT ) VALUES (
7, 'A', TO_Date( '12/14/2001 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
commit;
the only query i found :

select
*
from
test etat_i
where
etat_i.etat not in (
select distinct
etat_f.etat
from test etat_f
where
etat_f.id>etat_i.id
and etat_f.id < (
select min(etat_s.ID)
from
test etat_s
where
etat_s.etat != etat_i.etat
and etat_s.ID > etat_i.ID
)
)
;

If gurus got a better query....

[Lilian BRUN]

If you want to try :

CREATE TABLE TEST
(
ID NUMBER primary key,
ETAT VARCHAR2(1),
DATE_CREAT DATE
);

INSERT INTO TEST ( ID, ETAT, DATE_CREAT ) VALUES (
1, 'A', TO_Date( '10/10/2001 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TEST ( ID, ETAT, DATE_CREAT ) VALUES (
2, 'B', TO_Date( '10/11/2001 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TEST ( ID, ETAT, DATE_CREAT ) VALUES (
3, 'B', TO_Date( '10/12/2001 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TEST ( ID, ETAT, DATE_CREAT ) VALUES (
4, 'C', TO_Date( '11/12/2001 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TEST ( ID, ETAT, DATE_CREAT ) VALUES (
5, 'C', TO_Date( '12/10/2001 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TEST ( ID, ETAT, DATE_CREAT ) VALUES (
6, 'C', TO_Date( '12/13/2001 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TEST ( ID, ETAT, DATE_CREAT ) VALUES (
7, 'A', TO_Date( '12/14/2001 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
commit;
the only query i found :

select
*
from
test etat_i
where
etat_i.etat not in (
select distinct
etat_f.etat
from test etat_f
where
etat_f.id>etat_i.id
and etat_f.id < (
select min(etat_s.ID)
from
test etat_s
where
etat_s.etat != etat_i.etat
and etat_s.ID > etat_i.ID
)
)
;

If gurus got a better query....

[Lilian BRUN]

If you want to try :

CREATE TABLE TEST
(
ID NUMBER primary key,
ETAT VARCHAR2(1),
DATE_CREAT DATE
);

INSERT INTO TEST ( ID, ETAT, DATE_CREAT ) VALUES (
1, 'A', TO_Date( '10/10/2001 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TEST ( ID, ETAT, DATE_CREAT ) VALUES (
2, 'B', TO_Date( '10/11/2001 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TEST ( ID, ETAT, DATE_CREAT ) VALUES (
3, 'B', TO_Date( '10/12/2001 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TEST ( ID, ETAT, DATE_CREAT ) VALUES (
4, 'C', TO_Date( '11/12/2001 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TEST ( ID, ETAT, DATE_CREAT ) VALUES (
5, 'C', TO_Date( '12/10/2001 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TEST ( ID, ETAT, DATE_CREAT ) VALUES (
6, 'C', TO_Date( '12/13/2001 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TEST ( ID, ETAT, DATE_CREAT ) VALUES (
7, 'A', TO_Date( '12/14/2001 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
commit;
the only query i found :

select
*
from
test etat_i
where
etat_i.etat not in (
select distinct
etat_f.etat
from test etat_f
where
etat_f.id>etat_i.id
and etat_f.id < (
select min(etat_s.ID)
from
test etat_s
where
etat_s.etat != etat_i.etat
and etat_s.ID > etat_i.ID
)
)
;

If gurus got a better query....