Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: GROUP BY query in Oracle

Re: GROUP BY query in Oracle

From: C. William Grant <cwgrant_at_ix.netcom.com>
Date: Sun, 15 Aug 1999 19:19:19 -0400
Message-ID: <37B74AF7.A34ECAC4@ix.netcom.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US