none
using distinct on one column only

    Question

  • I have a table that contains 5 or 6 columns. (plantid, commonName, scientificName, planttype, etc)  what I am wanting to do is create a table of all the plants with distinct scientificNames.   but I am wanting to display the commonName, scientificName, and planttype.    I figured using the distinct operator would work, but is seems as if it looks at the combination of all 3 rows instead of being distinct on only one row. 

    Basically I am wanting to be distinct on one row, but view 3 different rows.  Can anyone show me the sql statement to do this. 
    Thursday, March 16, 2006 5:39 PM

Answers

  • Take AdventureWorks sample DB for example, this query will match your need:

    WITH temp AS
    (
     SELECT *,
      ROW_NUMBER() OVER(PARTITION BY JobTitle ORDER BY JobTitle) AS rn
     FROM [AdventureWorks].[Sales].[vSalesPerson]
     )
     select * from temp where rn=1
    
    

    Mark as Answer if it helps. This posting is provided "AS IS" with no warranties, confers no rights.
    Friday, February 18, 2011 8:52 AM
  • Well, the best you'd be able to do is see one of the duplicate row's data for the other columns in your query.  For example you could create a SQL statement like the following that will group the results by the scientificName value and return the max() values for the other columns, like this:

    select max(commonName), max(planttype), scientificName
    from tableA
    group by scientificName

    You could also use any of the aggregate functions in place of the max() operator. 

    However, I'm guessing that the commonName and plantType may be different for records that have the same scientificName, and you may want to get the commonName and plantType values from a specific record...is that the case or no?

    Thursday, March 16, 2006 5:50 PM

All replies

  • Well, the best you'd be able to do is see one of the duplicate row's data for the other columns in your query.  For example you could create a SQL statement like the following that will group the results by the scientificName value and return the max() values for the other columns, like this:

    select max(commonName), max(planttype), scientificName
    from tableA
    group by scientificName

    You could also use any of the aggregate functions in place of the max() operator. 

    However, I'm guessing that the commonName and plantType may be different for records that have the same scientificName, and you may want to get the commonName and plantType values from a specific record...is that the case or no?

    Thursday, March 16, 2006 5:50 PM
  • o
    Thursday, March 16, 2006 6:02 PM
  • that is pretty much the case. 
    Thursday, March 16, 2006 6:15 PM
  • Could it be something like

    select plantid, commonName, scientificName, plantType from plants where scientificName in (select distinct scientificName from plants);

    ?

     

     

    Monday, March 20, 2006 9:44 PM
  • skip Distinct and add GROUP BY at the end:

     

    Select plantid, commonName, scientificName, planttype FROM table GROUP BY scientificName

    Wednesday, November 07, 2007 1:41 PM
  • Essentially you are doing 2 things.

     

    1. Show distinct values ScientificNames

    2. Show associated data like CommonName

     

    You can not do this with 1 result set, if 1 scientificname can have more than 1 CommonName

     

    What you can do is get the values for all the Scientificnames that are distinct

     

    Select * from  YOURTABLE where

    Scientificname is in

    (Select Distinct Scientificname, count(*) from [YOURTABLE] having Count(*) = 1)

     

     

     

     

     

     

     

    Wednesday, November 07, 2007 3:18 PM
  • tweak your query like this

     

    select min(id) as id,emailid
    from table
    group by emailid

     

    use an aggregate function for the column you want to take with the other column

    and group by the column you want to take distinct

     

    This will give you distinct emailid and their corresponding id

    • Proposed as answer by Parag Agrawal Monday, June 21, 2010 9:51 AM
    Tuesday, July 15, 2008 7:32 AM
  • You can use the following query to select the columns you want with only scientificName being distinct.
    SELECT DISTINCT (scientificName), commonName, planttype FROM tableA;


    Monday, August 03, 2009 7:51 AM
  • but this shows all the columns they do show only the distinct(scientificname)
    Wednesday, August 05, 2009 10:17 AM
  • No Distinct is not the correct answer. 

    DISTINCT would give you distinct across all columns and not for a particular column.


    Friday, August 07, 2009 12:32 PM
  • Take AdventureWorks sample DB for example, this query will match your need:

    WITH temp AS
    (
     SELECT *,
      ROW_NUMBER() OVER(PARTITION BY JobTitle ORDER BY JobTitle) AS rn
     FROM [AdventureWorks].[Sales].[vSalesPerson]
     )
     select * from temp where rn=1
    
    

    Mark as Answer if it helps. This posting is provided "AS IS" with no warranties, confers no rights.
    Friday, February 18, 2011 8:52 AM
  • ^ NO, you just wasted 5 mins of my life.  Your example doesn't solve the op question whatsoever.  Why post something that is obviously wrong?
    Friday, June 17, 2011 7:09 PM