Re: query question

Ci-Ci Mills wrote:

> I am trying to come up with a single SQL statement that will print out a
> list of addresses grouped by even and odd addresses. My DB has field called
> house_number which is a VARCHAR2, so I am using the last character of that
> field to determine if it is even/odd. What I have right now is:
>
> select
> decode(substr(house_number,-1),'0',house_number,'2',house_number,'4',house_n
> umber,'6',house_number,'8',house_number,NULL) EVEN,
>
> decode(substr(house_number,-1),'1',house_number,'3',house_number,'5',house_n
> umber,'7',house_number,'9',house_number,NULL) ODD,
> from member;
>
> However, I don't want two columns in the output, I want just one grouped by
> EVEN, ODD. I'm sure there is an obvious answer, but I am on brain-freeze.
> Thanks for any suggestions.
>
> cici_at_erols.com

try this,

select decode(mod(substr(house_number,-1),2),0,'even','odd') even_odd,

          house_number
from member
group by decode(mod(substr(house_number,-1),2),0,'even','odd'); Received on Thu Jul 29 1999 - 08:07:00 CDT