Re: GROUP BY query in Oracle

Zile:

[Quoted] No problem. What you are really trying to do is to group by the SSN and the Month portion of the date.
[Quoted] The query will look something like:

SELECT ssn, MIN(date_field), MAX(date_field) FROM tbl GROUP BY ssn, SUBSTR(TO_CHAR(date_field),1,2)

This syntax is approximate; I am on the road and away from my doc set at the moment.
[Quoted] The other thing you might want to consider is that what you have asked for only works
for a single year. The minute you cross year boundries, grouping by the month part no
[Quoted] longer gives you what you want. I would be temped to use the year and the month:

SELECT ssn, MIN(date_field),MAX(date_field) FROM tbl GROUP BY ssn, TO_CHAR(date_field,'YYYY-MM')

Good luck.

Bill Grant
Nautilus Consulting Group, Inc.
Westlake, Ohio

Zile Liao wrote:

> 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?
>
> Thanks alot!!!!!
>
> Zile Liao
> zliao_at_lbisoft.com
Received on Mon Aug 16 1999 - 01:19:19 CEST