Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53

    Question Unanswered: Select group of records w/ only 1st duplicate record

    Hi,

    I have a problem that I can't seem to find much help for. I have a table that has duplicate IDs for multiple sections of data. This isn't a mistake, it's by design, so deleting it isn't an option. What I'd like to do is select all the records from the table, and if a duplicate is found, only displaying the first record from the duplicate. I've seen lots of posts about finding duplicate records, displaying only duplicate records, or displaying only non-duplicate records, but I haven't been able to find any information on how to do this.

    Here's an example of my table:

    tblSID
    ----------------------------------------------------------
    SID | OnStreet | FromStreet | ToStreet |
    ----------------------------------------------------------
    100 Water St. 9th St. 10th St.
    101 Water St. 10th St. 11th St.
    101 Water St. 10th St. Averis Alley
    102 Water St. 11th St. 12th St.


    Ideally, my results would be this:

    100 Water St. 9th St. 10th St.
    101 Water St. 10th St. 11th St.
    102 Water St. 11th St. 12th St.


    Any help would be greatly appreciated.

    Thanks!
    -T

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >only displaying the first record from the duplicate.
    You have a basket of colored balls. There are 3 red balls in the basket.
    Which red ball is the first one?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,098
    Provided Answers: 4
    In other words, what candidates '11th St.' as a result (as opposed to 'St. Averis Alley')?

  4. #4
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53
    It really doesn't matter to me, so long as one is selected and the rest ignored.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select SID
         , max(OnStreet) as OnStreet
         , min(FromStreet) as FromStreet
         , max(ToStreet) as ToStreet
      from tblSID
    group
        by SID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53
    That worked great, thanks!

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,098
    Provided Answers: 4
    Quote Originally Posted by Tarkon
    It really doesn't matter to me, so long as one is selected and the rest ignored.
    So why didn't you tell so in your first post? You'd probably get the answer much earlier.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Tarkon
    That worked great, thanks!
    you're welcome

    so you didn't really need an "intact" row after all, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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