Re: NEED SQL HELP PLEASE, SELECT DISTINCT QUESTION

Nick Nobbe wrote: > > Dear Zig, > Use group by, as in > > select colA, colB, colC > from yourtable > group by 1,2,3;> > Check out the tutorial - one of the best pieces of documentation Informix > has done for beginning dbas and sqlers. > > Hope this helps, > Nick > > At 11:31 AM 10/2/98 -0400, zigzag wrote: > >I have a table which contains several columns. I would like to retrieve > >the rows from this table, but remove duplicates based on one column > >only. > > > >For example: > > > >Row# Col A Col B Col C > >1 cat AA BB > >2 cat BB CC > >3 dog CC DD > >4 hat DD EE > >5 dog EE FF > > > >I want to select rows so that they are unique on Col A only, so the set > >returned would only > >contain rows, 1,3 and 4. > > > >If I was writing this Select in my own language it would be something > >like this: > >"Select ColA, ColB, ColC from table where ColA is unique." > > > >Of course if I only want ColA, it's easy: > >"Select distinct ColA from table;" > > > >Why am I having such a hard time with this seemingly easy chore? > >ZZ > > > >Please reply via email and newsgroup > > > > > > > ********************* > Nick Nobbe > NLS/BPH > Library of Congress > nnob@loc.gov This will not do what is required. The problem is selecting an arbitrary row from a set. The set is defined by ColA. SQL has no facilities for selecting a number of rows from an arbitrary set without going round the houses. I have had this problem too. There are several possible approaches, all inefficient to some extent. Rajesh Kapur's solution should work where rowids are in use. This must be a requirement for data warehousing and exploratory data analysis: an arbitrary (better, random) subset, either a defined % of the population set rows or a fixed number of rows. -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 --- If all else fails, read the instructions and the release notes. Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/ ---