Re: grouping query

"rj" <rjeena_at_gmail.com> a écrit dans le message de news: 1143454135.713714.90830_at_j33g2000cwa.googlegroups.com...
| Hi
|
| i have a test table with many diff columns.Im using oracle 9i
|
| i gna jus name 3 columns Name, Start_time, Identifier
|
| example of data:
|
| NAME START_TIME IDENTIFIER
|
| Talk show 05:00:00 P
| A 05:15:00 I
| B 05:20:00 I
| C 05:22:00 I
| D 05:24:00 I
| Ridzys Chicken 06:00:00 S
| talk show 06:10:00 P
| A 06:15:00 I
| B 06:20:00 I
| C 06:22:00 I
| D 06:24:00 I
| Ridzys Chicken 06:30:00 S
|
| how do i find P insert all its column data into a new table. then i
| need
| only the start_time for the I and then the entries for S to look as
| follows.
|
|
| Name Start_Time Identifier
|
|
| Talk show 05:00:00 P
| E 05:15:00 I
| Ridzys Chicken 09:00:00 S
| talk show 06:10:00 P
| z 06:15:00 I
| Ridzys Chicken 06:30:00 S
|
|
| the table has lots of identifiers i need to group them all as above.
|
| Thanks in advance :)
|

If i understand, you want the P, the S and only the first I (I don't understand where E and z come from). You can do something like:
select name, start_time, identifier
from ( select name, start_time, identifier,

          lag(identifier) over (order by start_time) prev    from mytable )
where identifier in ('P','S') or prev != 'I' /

Regards
Michel Cadot Received on Mon Mar 27 2006 - 09:33:31 CST