using distinct on one column only

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.

Answer this question

using distinct on one column only

adnap
o
Bito
skip Distinct and add GROUP BY at the end:

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

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
freewind
Could it be something like

select plantid, commonName, scientificName, plantType from plants where scientificName in (select distinct scientificName from plants);
Damien White
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)
c&#35;_novice
that is pretty much the case.
using distinct on one column only

Answer this question