Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2013

    Unanswered: How to select distinct record?


    I have a table, as follows:

    ID | Name | MyID
    1 | Alan | 2
    2 | Alan | 2
    3 | John | 3

    I want to select the records as follows:

    1. If two records have the duplicate values in ‘Name’ column, then only the first record is selected and the other record is discarded, even if the two records have different values in other columns.
    2. If two records have different ‘Name’ value, then they are ordered via ‘MyID’ column in ascendant order.
    3. All columns of the records should be returned.

    So for the above records, the query should select the following ones:

    ID | Name | MyID
    1 | Alan | 2
    3 | John | 3

    How to write the query? It seems I should use DISTINCT or GROUP BY, but my queries are all fail. Please help.


  2. #2
    Join Date
    Dec 2007
    Richmond, VA
    Provided Answers: 5
    as they should what would be distinct between ID 1 and 2, when you are selecting the ID? Seems like a bit of school work here, so why don't you try showing us some of your work(SQL statement) and we can nudge you in the direction. I'll give you a head start.

    1. look at a MIN subselect
    2. try an ORDER
    3. Its all in what you select.

  3. #3
    Join Date
    Oct 2010
    Atlanta, GA
    Hi chcw, there are several options. Try this:

    --Create temp table
    create table #myTable (ID int, Name varchar(10), MyID int)
    insert into #MyTable VALUES (1,'Alan',2),(2,'Alan',2),(3,'John',3)
    --Verify records in temp table
    SELECT * FROM #myTable
    --Select statement using CTE to seperate records by Name
    ;WITH cte AS (SELECT *,ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY [Name] ASC) AS ordinal FROM #myTable)
    SELECT ID, [Name], MyID FROM cte WHERE ordinal = 1

Tags for this Thread

Posting Permissions

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