Re: GROUP BY query in Oracle
Date: Fri, 20 Aug 1999 15:52:59 +0000
Message-ID: <37BD79DB.923D748E_at_livingonline.com>
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
>
[snip]
>
> 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!!!!!
I deduce you are actually trying to break things by month. So you need something like
select ssn, to_char(datefield, 'mm'), min(datefield), max(datefield)
from table
group by ssn, to_char(datefield, 'mm')
If you don't want to see the month, put the above into a view and select what you need from the view (I'm real curious if there is a way to get the desired result without a view).
To answer your question regarding the GROUP BY: Oracle brings back all the rows satisfying your WHERE clause, divides them up according to your GROUP BY criteria, then applies you functions to each group.
-- Phil Singer |psinger1_at_livingonline.com(home) Oracle DBA & Webmaster |psinger1_at_ford.com (work) "Now with the wisdom of years, I like to reason things out. And thhe only people I fear are those who never have doubts" ....Billy JoelReceived on Fri Aug 20 1999 - 17:52:59 CEST