Results 1 to 7 of 7
  1. #1
    Join Date
    May 2003
    Posts
    11

    Unanswered: remove duplicates but display all columns

    hi i hav a table as such:
    id fld1 fld2 updatedate
    ------------------------------
    1 aaa aaa 14-jan-2003
    2 bbb bbb 14-jan-2003
    3 ccc ccc 15-jan-2003

    and i wish to generate a result set that shows rows where the updatedate is unique but i want to include ALL columns in that result set. If there are rows with the same updatedate i just want the first one.
    Last edited by deekay2003; 09-22-03 at 22:59.

  2. #2
    Join Date
    Sep 2003
    Posts
    14
    Hi,

    Please try

    select id, fld1, fld2, distinct updatedate from table name

    I am not sure if it is possible to get all distinct columns without
    lisiting all the column names in the query

    Reg
    Usha

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select id, fld1, fld2, updatedate
    from yourtable zz
    where id =
    ( select min(id)
    from yourtable
    where updatedate = zz.updatedate )

    rudy
    http://r937.com/

  4. #4
    Join Date
    Dec 2004
    Posts
    22
    In a similar vein, I would also like to remove duplicate rows while displaying all columns in the query. Clnumber is the column I want to key on for duplicates. Here is a very slimmed down (there are many more columns & joins) example of the query with some sample data:

    select rolodex.ipname, clients.clnumber, classes.class, classes.date
    from clients
    join rolodex on clientid = rolodexid
    join classes on clientid = classid
    where rolodexid = 3

    ipname clnumber class date
    -----------------------------------
    smith 11111 320 2004-12-21
    smith 11111 420 2004-12-22
    barnes 22222 320 2004-12-21
    jones 33333 320 2004-12-21
    jones 33333 320 2004-12-21

    Here is what I want the results to be (I don't care which of the duplicate rows get eliminated, although I suppose I could use class with an aggregate like in Rudy's prior example):
    ipname clnumber class date
    -----------------------------------
    smith 11111 320 2004-12-21
    barnes 22222 320 2004-12-21
    jones 33333 320 2004-12-21

    I tried the following but got no results:
    select * from
    (
    select rolodex.ipname, clients.clnumber, classes.class, classes.date
    from clients
    join rolodex on clientid = rolodexid
    join classes on clientid = classid
    where rolodexid = 3
    ) As IPN
    Where class =
    (Select MIN(class) FROM classes
    where clnumber = IPN.clnumber)

  5. #5
    Join Date
    Dec 2002
    Posts
    134
    Quote Originally Posted by fstop
    In a similar vein, I would also like to remove duplicate rows while displaying all columns in the query. Clnumber is the column I want to key on for duplicates.
    Below is vendor specific(DB2) SQL using OLAP functions. I think Oracle should support the same syntax (except temp table).

    with temp(ipname, clnumber, class, date) as
    (values
    ('smith', 11111, 320, '2004-12-21'),
    ('smith', 11111, 420, '2004-12-22'),
    ('barnes', 22222, 320, '2004-12-21'),
    ('jones', 33333, 320, '2004-12-21'),
    ('jones', 33333, 320, '2004-12-21')
    )

    select ipname, clnumber, class, date from
    ( select
    ipname, clnumber, class, date,
    rownumber() over (partition by clnumber) as r
    from temp) t
    where r = 1


    IPNAME CLNUMBER CLASS DATE
    ------ ----------- ----------- ----------
    smith 11111 320 2004-12-21
    barnes 22222 320 2004-12-21
    jones 33333 320 2004-12-21

    3 record(s) selected.

  6. #6
    Join Date
    Dec 2004
    Posts
    22
    Thanks for your reply, chuzhoi. I'm on SQL Server, so I'm not sure if this will work. However, I will look into it. I appreciate the effort.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    For MS-SQL, and assuming there were no time values in the updatedate column, I'd use:
    Code:
    SELECT *
       FROM myTable AS a
       WHERE  a.id = (SELECT Min(b.id)
          FROM myTable AS b
          WHERE  b.updatedate = a.updatedate)
    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •