From oracle-l-bounce@freelists.org Thu Apr 15 14:12:36 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i3FJCUq21259 for ; Thu, 15 Apr 2004 14:12:30 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i3FJCP621250 for ; Thu, 15 Apr 2004 14:12:30 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E7899637EEF; Thu, 15 Apr 2004 13:35:14 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 28748-16; Thu, 15 Apr 2004 13:35:14 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4723F639E7B; Thu, 15 Apr 2004 13:10:15 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 15 Apr 2004 13:09:04 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0DFAF63A6FC for ; Thu, 15 Apr 2004 12:52:08 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 18131-84 for ; Thu, 15 Apr 2004 12:52:07 -0500 (EST) Received: from mail.acelerate.com (mail.acelerate.com [200.105.128.132]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id 153AB63A17D for ; Thu, 15 Apr 2004 12:20:08 -0500 (EST) Received: (qmail 30332 invoked from network); 15 Apr 2004 16:55:03 -0000 Received: from dazasoftware.com (HELO org48l7d9ara8b) (200.105.151.94) by 0 with SMTP; 15 Apr 2004 16:55:03 -0000 Message-ID: <001301c4230e$f73beb30$2501a8c0@dazasoftware.com> From: "Juan Cachito Reyes Pacheco" To: References: Subject: Re: SQL to retrieve all distinct records Date: Thu, 15 Apr 2004 13:27:41 -0400 MIME-Version: 1.0 Content-type: text/plain; charset=Windows-1252 Content-Transfer-Encoding: 8bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1409 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 3176 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jreyes@dazasoftware.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org I think your approach is right if you want to return one row from every discint a,b group. I suppose select * from testing Juan Carlos Reyes Pacheco OCP Database 9.2 Standard Edition ----- Original Message ----- From: "Alison Barak" To: Sent: Thursday, April 15, 2004 11:23 AM Subject: RE: SQL to retrieve all distinct records I believe I have found a solution. Just wonder if anyone else has an alternative or better approach? select * from testing where rowid in (select min(rowid) from testing group by a,b); thanks. alison >From: "Alison Barak" >To: oracle-l@freelists.org >Subject: SQL to retrieve all distinct records >Date: Thu, 15 Apr 2004 09:38:28 -0400 > >Hi, >can someone assist me with formulating a query to retrieve all distinct >records. I need to retrieve all three columns a,b &c based on the distinct >of the two columns a & b. > >SQL> select * from testing; >A B C >---------- ---------- ---------- >APPS APPS R1 >SYS SYS2 R2 >SYS SYS2 R3 >DOC DOC2 R4 >DC DC2 R5 >DOC DOC R6 > >The result should be >A B C >---------- ---------- ---------- >APPS APPS R1 >SYS SYS2 R2 >DOC DOC2 R4 >DC DC2 R5 >DOC DOC R6 > > >I tried using group by/having count but was not successful. Any idea? > >Thanks. > >alison > _________________________________________________________________ Persistent heartburn? Check out Digestive Health & Wellness for information and advice. http://gerd.msn.com/default.asp ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------