Re:messy SELECT & GROUPing

Hi Ramiro, You need to concatenate the first five fields and group by the concatenated value. For eg, if your fields are named col1, col2, col3 .... col8, what you need is a SQL statment as follows:- select col1 || col2 || col3 || col4 || col5, count(*) from table group by 1 having count(*) > 4 Of course, if your fields lengths are longer than the actual, remember to trim them and add a space between each field to get the output you require. I hope this helps Sujata ____________________Reply Separator____________________ Subject: messy SELECT & GROUPing Author: <cdamo@indec.mecon.ar > Date: 1/26/98 10:34 AM I have a table which contains 8 fields. Each row represents a geographic zone. The fields are all CHAR fields containing country codes, state codes, city codes an so on (don't ask me why we use CHAR type). The first five fields identifies an 'area'. The three others represents smaller zones in that area. I need to know if there is an area which contains more than four zones, beeing a zone a three-code identification. WHAT A MESS! (because of my poor English and my DB design) Example: (a common record set in my table) ___ area __ _zone_ / \\ / \\ 36 086 0 4 16 22 22 00 36 086 0 4 16 22 22 01 36 086 0 4 17 01 01 00 36 086 0 4 17 01 02 00 36 086 0 4 18 01 01 00 36 086 0 4 19 01 01 01 .. 36 086 0 5 01 01 01 00 .. I tried GROUPing BY the firs 5 columns and COUNTing the rest, but I can't count unique-three-filed-group. I'd like to write a single SELECT statement which returns 5-field-area 3-filed-unique-count In the above example: 36 086 0 4 16 2 36 086 0 4 17 2 36 086 0 4 18 1 36 086 0 4 19 1 .. 36 086 0 5 01 1 .. I'm trying to avoid declaring a cursor and verifying a change of area after each fetch. TIA. Ramiro Rela Buenos Airea, Argentina. P.S. Please reply to this address 'cause I'm not subscribed to the list at the moment. Sorry if my question has been answered already.