Results 1 to 3 of 3

Thread: Selecting Only the first instance of a value?

  1. #1
    Join Date
    Dec 2006
    Posts
    1

    Selecting Only the first instance of a value?

    Okay I'm in the process of cleaning up some very large address lists. Individuals have their names spelled several different ways, however the address are good. I need to create an internal list for of their names and address [their must be a name value]. The issue I have is that I need to select a single value out (without preference) from several out of a table.

    Here is an example

    Table 1.
    Jim, 1
    Jimm, 1
    Amy, 2
    Amee, 2

    Table 2.
    1, Someotherinfoabout1
    2, someotherinfoabout2

    Wanted Result:
    Jim, 1, Someotherinfoabout1
    Amy, 2,someotherinfoabout2

    I've been trying to find the SQL to do this all day, Any suggestions???

  2. #2
    Join Date
    Oct 2005
    Posts
    2,557
    If you don't care about the variation in names (and why are you sure the addresses are "good" if the names can be so hosed?), then de-dupe the records based on address.

  3. #3
    Join Date
    Dec 2004
    Posts
    502
    If you don't care which name is selected, then how about this:

    SELECT MIN(A.name) AS name, B.id, B.otherinfo
    FROM table1 AS A JOIN table2 AS B
    ON A.id = B.id
    GROUP BY B.id, B.otherinfo

Posting Permissions

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