Help on SQL statement

[Leon1977]

I have a table with this data
varchar2(1)| date | date
-----------|--------|--------
a 12:10 12:11
a 12:12 12:12
a 12:14 12:15
b 13:10 13:11
b 13:12 13:12
b 13:14 13:15
a 14:10 14:11
a 14:12 14:12
a 14:14 14:15
c 14:30 14:31
c 14:32 14:32
c 14:34 14:35

I want a single select statement which will return
in this case 4 rows like this
a 12:10 12:15
b 13:10 13:15
a 14:10 14:15
c 14:30 14:35

Thanx in advance

 

[isonlyme]

Hi, try this

Field1 will be the abcd data

select distinct field1 from tablename

hope this help

 

[sem]

select col1, min(col2), max(col3) group by col1 Regards, Dima

 

[isonlyme]

you were right dima thanks

 

[Leon1977]

no
in that case it only returns 3 rows
it should return 4 rows (a - twice)
look for every type i need its periods
but in that case "a" has two periods so I need two rows for it...

 

[sem]

Your task is a bit fuzzy. Try this

select col1, min(col2), max(col3), trunc(col2,'hh')
group by col1, trunc(col1,'hh')

But what should be the result in the case of overlapping?

a 14:10 15:11
a 14:11 15:12

Regards, Dima

 

[Leon1977]

there will never be case of overlapping
but what Dima wrote works in the case I used to explain but
a type "a" could appear in a certain hour many times
Look at my exam and pretend there is one more row /one more period/:

a 14:36 14:50

 

[sem]

I suppose the result will be correct

a 14:10 14:50 (14:00)

Did you try

select col1, min(col2), max(col3), trunc(col1,'hh')
group by col1, trunc(col1,'hh')

Regards, Dima

 

[Leon1977]

yes I tried it but if same period type appears in certain hour lets say
a from 11:00 - 11:10
b from 11:15 - 11:30
a from 11:45 - 11:50
your query will return two rows
a 11:00 11:50
b 11:15 11:30
but the correct query in this case should return 3 trows

 

[sem]

Can you explain WHAT should this query return? Regards, Dima

 

[Leon1977]

Well ok.. let me explain first what it is
something happens at certain point of time
there are several types each has a period
lets say types are a, b, c, d ...
type occur end
---------------------
a 11:00 12:00
a 12:10 12:15
b 12:20 12:30
a 12:40 12:41
c 12:45 15:55

I need the query to return for every type it start and end time but they should be grouped only if next one is from the same type example
a,a,a,a,b,a,a,b,b,c,a,c should retrun a,b,a,b,c,a,c
and cccccccccccccccccccccc,a,c - c,a,c
/pardon my english /

 

[AdHoc]

I don't know how to prove this is impossible in plain SQL but if I had this task I would do it in Crystal Reports. If you don't have Crystal Reports, do you have MS-Access or some other reporting tool? If not, you can write a script in PL/SQL to make the report you need. You need a reporting tool or a procedural language. (Now if someone comes up with an SQL statement that does exactly what you want I will look dumb.:~/)