Re: GROUP BY query in Oracle

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: 16 Aug 1999 11:46:15 GMT
Message-ID: <7p8tm7$knf$3@news.seed.net.tw>

Zile Liao <zliao_at_lbisoft.com> wrote in message news:7p6klj$28t$1_at_autumn.news.rcn.net...
> Could anyone tell me how to group dates that are in sequence?
>
> My data looks like the following:
>
> 111-11-1111 1/1/99
> 111-11-1111 1/2/99
> 111-11-1111 1/3/99
> 111-11-1111 2/1/99
> 111-11-1111 2/2/99
> 111-11-1111 2/3/99
> 222-22-2222 1/1/99
> 222-22-2222 1/2/99
> 222-22-2222 1/3/99
>
> When I used the following statement:
>
> SELECT ssn, MIN(date_field), MAX(date_field) FROM tbl GROUP BY ssn
>
> It returned
> 111-11-1111 1/1/99 2/3/99
> 222-22-2222 1/1/99 1/3/99
>
> I would actually like to get 3 records back as the following:
> 111-11-1111 1/1/99 1/3/99
> 111-11-1111 2/1/99 2/3/99
> 222-22-2222 1/1/99 1/3/99
>
> Could anyone tell me if it is possible?

Add a expression in the GROUP BY clause:

SELECT ssn, MIN(date_field), MAX(date_field) FROM tbl
GROUP BY ssn, trunc(date_field, 'MM'); Received on Mon Aug 16 1999 - 06:46:15 CDT