Oracle Database :: simple sql question


	simple sql question - group by having ? I know this works Select columna From tablea Group by columna Having count() > 1 But how can get columnb and columnc of the tablea table if i don't want to group by those columns? A join? a union? jobs wrote: > I know this works > Select columna From tablea Group by columna Having count() > 1 > But how can get columnb and columnc of the tablea table if i don't > want to group by those columns? > A join? a union? SELECT cola, colb, colc FROM t WHERE cola IN (SELECT cola FROM (your_query_here)); One way among many. Also look at using EXISTS. In the future always include your version when you post. -- Daniel A. Morgan University of Washington damor@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org -----------------------------------------------Reply----------------------------------------------- DA Morgan wrote: > jobs wrote: > SELECT cola, colb, colc > FROM t > WHERE cola IN (SELECT cola FROM (your_query_here)); > One way among many. Also look at using EXISTS. Actually no need to scan table more than once. assuming you haven't too ancient Oracle version: SQL> desc test Name Null? Type ----------------------------------------- -------- --------------------- TST_ID NOT NULL NUMBER TST_OBJ_NAME VARCHAR2(100 CHAR) SQL> ed Wrote file afiedt.buf 1 select tst_obj_name, count() 2 from test 3 group by tst_obj_name 4 having count() >10 SQL> / TST_OBJ_NAME --------------------------------------------------------------------------- ----- COUNT() ---------- COSTS 13 COSTS_PROD_BIX 13 COSTS_TIME_BIX 13 SALES 17 SALES_CHANNEL_BIX 17 SALES_CUST_BIX 17 SALES_PROD_BIX 17 SALES_PROMO_BIX 17 SALES_TIME_BIX 17 9 rows selected. SQL> ed Wrote file afiedt.buf 1 select * from ( 2 select tst_obj_name, tst_id, count() over (partition by tst_obj_name) cnt 3 from test 4 ) 5 where cnt > 10 SQL> / TST_OBJ_NAME --------------------------------------------------------------------------- ----- TST_ID CNT ---------- ---------- COSTS 28572 13 COSTS 28575 13 COSTS 28577 13 <skipped> Hopefully original OP will be able to adjust this to your table. Analytic functions rules! :) Gints Plivna http://www.gplivna.eu

MS Dynamics CRM 3.0

Oracle Database

simple sql question - group by having ?