Hi Li Li,
If you need to eleminate duplicated, use 'GROUP BY' option in SELECT your 
statement to group the selected rows based on the value of a condition for 
each row and return a single row of summary info for each group. Also 
'HAVING option restrict the groups of rows returned to those groups.

If you need to display column of the same table with different criteria, use 
subquery in your SELECT statement and then use the resulting rows as a view 
in the FROM clause.

If you need to combine rows from two or more tables, perform a join whenever 
multiple tables apear in the FROM clause. Then select any columns from any 
of those tables.

Adam

========================================

From: Li Li <lilinola@u...>
Reply-To: "sql language" <sql_language@p...>
To: "sql language" <sql_language@p...>
Subject: [sql_language] Re: [Re: [Re: How to Elimination Duplicate Rows? 
(distinct one of the column)]]
Date: 27 Mar 2001 10:09:46 EST

Hi Adam,

I don't get it, could you please give more info?

Again, I am using Oracle database.  I have four tables joint, one Address
table have multiple rows addresses info for same company (e.g. one is P.O.
Box, another one is Physical Address) , I only need pull out one address.

Thanks for help,

Li Li


"Adam Dehkordi" <dehkordi@h...> wrote:
Hi Li LI,
use 'join' query to combine two or more rows from the same table.
see Joins in your SQL reference or if you can't find it, let me know.

Adam


From: Li Li <lilinola@u...>
Reply-To: "sql language" <sql_language@p...>
To: "sql language" <sql_language@p...>
Subject: [sql_language] Re: [Re: How to Elimination Duplicate Rows?
(distinct one of the column)]
Date: 26 Mar 2001 11:48:54 EST

Roger,

Thanks, I will try to do similar in the Oracle by using cursor.  I think it
will work.  We are now doing this filtering job in VB (for big company, it
takes more than 30 minute to get result), it may faster
using cursor running on server.

Thanks again,

Li Li

rnedel@b... wrote:
Li Li:

Others out there who are more knowledgable in SQL queries may be able to
accomplish what you want without the use of CURSORS.  I'm not that smart,
so I would choose to implement your solution using a cursor.  Be advised
that using cursors slows down your query since you literally iterate
through each row in the recordset.  For certain situations; however,
cursors provide the only solution.

Be advised that I use SQL Server rather than Oracle.  I am providing a
solution that will work on SQL Server.  You may have to tweak the code to
match Oracle's syntax.  Having said that...

====================================

USE MyDatabase
GO

-- Declare variables to hold data fetched from the recordset.

DECLARE @DocID int
DECLARE @CompanyName varchar(15)
DECLARE @Address varchar(24)
DECLARE @Parish varchar(11)
DECLARE @State varchar(6)

DECLARE @PrevDocID int

- Define the cursor that will hold the recordset data.

DECLARE MyCursor CURSOR FOR
SELECT DocID,
         CompanyName,
         Address,
         Parish,
         State
FROM [MyTable]
ORDER BY DocID

-- Open the cursor.

OPEN MyCursor

-- Initialize the previous doc ID to a value that will be
-- non-existant in the recordset.

SET @PrevDocID = -1

-- Print header columns.

PRINT 'DocID         CompanyName    Address                 Parish
State '
PRINT '-----------   ------------   ---------------------   --------   ----
- '

-- Fetch the recordset's first row into our local variables.

FETCH NEXT FROM MyCursor
INTO @DocID, @CompanyName, @Address, @Parish, @State

-- While we continue to successfully fetch rows from the recordset.

WHILE @@FETCH_STATUS = 0
BEGIN

    -- See if the newly fetched DocID is different
    -- from the last DocID fetched.

    IF (@DocID <> @PrevDocID)
    BEGIN

      -- Since this is the first row containing this distinct
      -- doc id, print out this row.

      PRINT LEFT(CONVERT(varchar(14), @DocID) + '              ', 14) +
            LEFT(@CompanyName + '               ', 15) +
            LEFT(@Address + '                        ', 24) +
            LEFT(@Parish + '           ', 11) +
            LEFT(@State + '      ', 6)

      -- Set the prev doc id to this new one in preparation for
      -- fetching the next row from the recordset.

      SET @PrevDocID = @DocID

    END

    -- Get the next row from the recordset.

    FETCH NEXT FROM MyCursor
    INTO @DocID, @CompanyName, @Address, @Parish, @State

END

-- Clean up.

CLOSE MyCursor
DEALLOCATE MyCursor

====================================

Hope this helps.

- Roger

=================================================

  > Hi Everyone,
  >
  > Anyone knows how to select many columns but only make one column
distinct
  > =3D
  > ?
  >
  > I used "distinct" in SQL statement join a few tables (Oracle), I got
data
  > =3D
  >
  > return like:
  >
  > DocID CompanyName Address Parish State 
  >
  >
  > 1234 Shell Corp. P.O. Box# Jefferson LA
  > 1234 Shell Corp. HW 1 LA
  > 1234 Shell Corp. P.O. Box# Jefferson
  >
  > But I only want distinct DocID, but I need display the other info too,
an
  > d
  > don't care which row.
  >
  > Fow example, I want result to be only one row for each DocID.
  >
  > Anyone can help?
  >
  > Li Li