SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: distinct

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    distinct

    Hi All,
    I have a table that includes duplicate values in one of the fields(housekey)
    I want to be able to query distinct records from this table, I need all the fileds and not just only one field. How can I accomplish this?
    my code

    select Distinct Huse_Hld_Key, T.*
    from TempTbl T

    only gives me Hust_hld_key

    Thanks
    Thanks

  2. #2
    SitePoint Addict danfran's Avatar
    Join Date
    Jan 2005
    Location
    New York City
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, this is subject is becoming quite popular this week..

    The reason why it isn't so easy is that you need an algorithm for removing duplicates. -It doesn't matter what it is, but you have to have one. Here are some choices you have for picking the lucky duplicate:
    * choose only the one with the most recent "CreateDate"
    * choose only the one with the highest WhateverID
    * the list goes on, but you must pick something to tell the db server..

    Let me ask, though, if you have 7 kinds of "housekeys" and 112 records in your table, what good are the extra fields in the 7 returned to you?

    Dan

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by danfran
    Well, this is subject is becoming quite popular this week..
    it's not just this week

    i've been answering this same question in various guises for several years

    i really wish i knew where the idea comes from that DISTINCT somehow operates on only the first column that comes after it, or even that it's a function, where the column is placed in parentheses, as in DISTINCT(colname)

    i have found that it helps to get them to see the actual duplicate rows as in post #3 here, where they can realize that their rows aren't unique, and then ask "which of these rows do you want to keep"

    they usually -- but not always! -- opt to keep the one with the highest id or something

    there was another thread here (wish i had bookmarked it) where some of the rows in the "duplicate group" had more data than others, so the strategy was to take the max of all the data columns when using GROUP BY, so that no data would be lost
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am select the data from a table call count and building the key call Huse_Hld_Key as
    Upper(substring(m.Lname,1,10) + substring(m.ad1,1,11) + substring(m.zip,1,3)).

    Now I want to select only the distinct records based on Huse_Hld_Key. For example:
    id fname Huse_hld_key
    12 zack Ahmed12345678901123
    13 zack Ahmed12345678901123

    I want to select only one of the record. Distinct does not help, what else can I do?
    Thanks
    Thanks

  5. #5
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your distinct does work.

    Your records are distinct from each other - they are made distinct because each of the values in the id field seems to be different.

    If you want only the distinct records based on fname and huse_hld_key then you should select only those two fields.


Bookmarks

Posting Permissions

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