Re: GROUP BY query in Oracle

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 Joel