Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: GROUP BY query in Oracle
Zile:
No problem. What you are really trying to do is to group by the SSN and
the Month portion of the date.
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.
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
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 Sun Aug 15 1999 - 18:19:19 CDT