SQL Query Multiple Columns Using Distinct on One Column Only

select * from tblFruit where
tblFruit_ID in (Select max(tblFruit_ID) FROM tblFruit group by tblFruit_FruitType)

you have various ways to distinct values on one column or multi columns.

• using the GROUP BY

  SELECT DISTINCT MIN(o.tblFruit_ID)  AS tblFruit_ID,
     o.tblFruit_FruitType,
     MAX(o.tblFruit_FruitName)
  FROM   tblFruit  AS o
  GROUP BY
       tblFruit_FruitType
  

• using the subquery

  SELECT b.tblFruit_ID,
     b.tblFruit_FruitType,
     b.tblFruit_FruitName
  FROM   (
         SELECT DISTINCT(tblFruit_FruitType),
                MIN(tblFruit_ID) tblFruit_ID
         FROM   tblFruit
         GROUP BY
                tblFruit_FruitType
     ) AS a
     INNER JOIN tblFruit b
          ON  a.tblFruit_ID = b.tblFruit_I
  

• using the join with subquery

  SELECT t1.tblFruit_ID,
      t1.tblFruit_FruitType,
      t1.tblFruit_FruitName
  FROM   tblFruit  AS t1
     INNER JOIN (
              SELECT DISTINCT MAX(tblFruit_ID) AS tblFruit_ID,
                     tblFruit_FruitType
              FROM   tblFruit
              GROUP BY
                     tblFruit_FruitType
          )  AS t2
          ON  t1.tblFruit_ID = t2.tblFruit_ID 
  

• using the window functions only one column distinct

  SELECT tblFruit_ID,
      tblFruit_FruitType,
      tblFruit_FruitName
  FROM   (
           SELECT tblFruit_ID,
                tblFruit_FruitType,
                tblFruit_FruitName,
                ROW_NUMBER() OVER(PARTITION BY tblFruit_FruitType ORDER BY tblFruit_ID) 
      rn
         FROM   tblFruit
      ) t
      WHERE  rn = 1 
  

• using the window functions multi column distinct

  SELECT tblFruit_ID,
      tblFruit_FruitType,
      tblFruit_FruitName
  FROM   (
           SELECT tblFruit_ID,
                tblFruit_FruitType,
                tblFruit_FruitName,
                ROW_NUMBER() OVER(PARTITION BY tblFruit_FruitType,     tblFruit_FruitName 
      ORDER BY tblFruit_ID) rn
            FROM   tblFruit
       ) t
      WHERE  rn = 1 
  

You must use an aggregate function on the columns against which you are not grouping. In this example, I arbitrarily picked the Min function. You are combining the rows with the same FruitType value. If I have two rows with the same FruitType value but different Fruit_Id values for example, what should the system do?

Select Min(tblFruit_id) As tblFruit_id
    , tblFruit_FruitType
From tblFruit
Group By tblFruit_FruitType

SQL Fiddle example