Re: {NJ} Query problem !

Hi, How about: select * from tab1 a where a.field1 IN (select b.field1 from tab1 b group by 1 having count(*) = (select MAX((select COUNT(*) from tab1 d where d.field1 = c.field1) ) from tab1 c) ) I hope this will work. Best Regards, Octav NAYAN JAIN wrote: > > Hi all, > > I have a query problem here . > > TABLE > > FIELD 1 FIELD 2 FIELD 3 > A 1 1 > B 2 2 > A 1 1 > B 2 2 > C 1 1 > A 1 1 > > field 2 and field 3 values are not of any importance. > > Now my requirement is tht i want to select all the records whose > count(*) of FIELD 1 is maximum > > So my result will look like > > FIELD 1 FIELD 2 FIELD 3 > A 1 1 > A 1 1 > A 1 1 > B 2 2 > B 2 2 > C 1 1 > > How can be this done is a single query statement > > Btw, I tried it this way > > select * from tab1 > where field1 in (select field1 , count(*) from tab1 > group by 1 > order by 2 desc)> > but obviously it will not work... I know (-; > > Another way of achieving this is > > create another temp table and there u put just the field1 and count(*) > values and then from > there u select the field1 values by ordering on count(*) field 2 of temp > table and retreive > records from main table. > > Doesnt seems challenging ! > > All help will be appreciated > > TIA, > > Nayan ! > > - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - > > "Dig a well before u r thirsty"