Re: GROUP BY query in Oracle

From: <teybrady_at_my-deja.com>
Date: Mon, 16 Aug 1999 20:39:53 GMT
Message-ID: <7p9sun$mmd$1@nnrp1.deja.com>

In article <37B74AF7.A34ECAC4_at_ix.netcom.com>, "C. William Grant" <cwgrant_at_ix.netcom.com> wrote:
> 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
>

I'm with Bill on this one...

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Aug 16 1999 - 15:39:53 CDT