Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2003
    Posts
    176

    Unanswered: DISTINCT on a single column

    Hi,

    I have a table of say 7 columns. I need to select all the columns but the DISTINCT clause should apply only to one column.

    Example:

    Name.......ID

    John.........1
    John.........2
    Mary.........3

    I need only one record for John. But both Name and ID should be selected.

    Thanks

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    "I need only one record for John. But both Name and ID should be selected."

    What value do you expect to be present in the ID field, given your example data?
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Sep 2003
    Posts
    176
    Any value in the ID field can be selected. It's only the name that matters.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ???????

    So what result to you want from:

    John.........1
    John.........2
    Mary.........3

    Option A:
    John, 1
    Mary, 3

    Option B:
    John, 2
    Mary, 3

    Option C:
    John, 1
    , 2
    Mary, 3

    Your logic is not clear.

    blindman

  5. #5
    Join Date
    Sep 2003
    Posts
    176
    Option A:
    John, 1
    Mary, 3

    Option B:
    John, 2
    Mary, 3


    I can do with either Option A or Option B. what ID is selected with John is immaterial. I don't want John to be reapeated. That's all.

    Thanks

  6. #6
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Can you provide the DDL for the table .. That might help a lot .. is the id an identity or unique column ???
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  7. #7
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Distinct is not the correct function to use here. Distinct eliminates any duplicate rows. It acts on the entire row not just a column.

    Try using Limit or TOP
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You want a group by query.

    Select Name, Min(ID)
    from YourTable
    Group By Name

    blindman

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What's "Limit"?

    blindman

  10. #10
    Join Date
    Sep 2003
    Posts
    176
    Here is the acual data

    Name.......ID...Dept..University...

    John........17....A..........XYZ
    John........18....B..........XYZ

    Now if I need only one John. Any record would do. How do I use the GROUP BY. What is this min function? My data has ID as char.

    Thanks

  11. #11
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    i think "limit" is in mysql and not in mssql ... wrong forum buddy
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Assuming ID is a unique value in your table (it better be, or your logic is not possible):

    select YourTable.*
    from YourTable
    inner join
    (select Name,
    Min(ID) ID
    from YourTable) DistinctIDs
    on YourTable.ID = DistinctIDs.ID

    This selects a single ID for each unique name and returns the data associated with that ID.

    blindman

  13. #13
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    "select Name,
    Min(ID) ID
    from YourTable"

    Doesn't a Group By clause need to be here.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah, that would probably help...

    blindman

Posting Permissions

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