From oracle-l-bounce@freelists.org Thu Apr 15 15:16:10 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i3FKG4402055 for ; Thu, 15 Apr 2004 15:16:04 -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 i3FKFx602046 for ; Thu, 15 Apr 2004 15:16:04 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1D2DC635A41; Thu, 15 Apr 2004 15:00:34 -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 11934-53; Thu, 15 Apr 2004 15:00:33 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id AFC97638468; Thu, 15 Apr 2004 14:12:03 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 15 Apr 2004 14:10:40 -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 DA11663945C for ; Thu, 15 Apr 2004 14:06: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 02834-05 for ; Thu, 15 Apr 2004 14:06:08 -0500 (EST) Received: from www.hotsos.com (hotsos.com [209.120.206.15]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id ED4B163525D for ; Thu, 15 Apr 2004 13:31:39 -0500 (EST) Received: from CVMLAP02 (66.169.163.33.ts46v-12.otne2.ftwrth.tx.charter.com [66.169.163.33]) (authenticated (0 bits)) by www.hotsos.com (8.12.11/8.11.0) with ESMTP id i3FIgJg2011691 for ; Thu, 15 Apr 2004 13:42:19 -0500 From: "Cary Millsap" To: Subject: RE: SQL to retrieve all distinct records Date: Thu, 15 Apr 2004 13:42:06 -0500 Message-ID: <004901c42319$5ce62e00$6901a8c0@CVMLAP02> MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.3416 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Importance: Normal In-Reply-To: <001301c4230e$f73beb30$2501a8c0@dazasoftware.com> X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 3184 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: cary.millsap@hotsos.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org ...as long as you don't mind that the row is selected at random. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com * Nullius in verba * Upcoming events: - Performance Diagnosis 101: 5/7 Dallas, 5/18 New Jersey, 6/22 Pittsburgh - SQL Optimization 101: 4/19 Denver, 5/3 Boston, 5/24 San Diego - Hotsos Symposium 2005: March 6-10 Dallas - Visit www.hotsos.com for schedule details... -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Juan Cachito Reyes Pacheco Sent: Thursday, April 15, 2004 12:28 PM To: oracle-l@freelists.org Subject: Re: SQL to retrieve all distinct records 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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 -----------------------------------------------------------------