[sql-server] SQL Query Multiple Columns Using Distinct on One Column Only

I am trying to write a SQL query that selects multiple columns from a table with the distinct operator on one column only.

The table is simple. The columns are:

tblFruit_ID, tblFruit_FruitType, tblFruit_FruitName
int          NVarChar            Text

I am trying to select all the tblFruit_FruitType with their corresponding tblFruit_ID.

I have tried:

Select Distinct(tblFruit_FruitType), tblFruit_ID FROM tblFruit

-Returns all results, not just distinct

Select tblFruit_FruitType, tblFruit_ID FROM tblFruit Group By tblFruit_FruitType

-Errors with Column tblFruit_ID is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Select tblFruit_FruitType, tblFruit_ID FROM tblFruit Group By tblFruit_FruitType, tblFruit_ID

-Returns all results, not just distinct

I also checked out these similar posts and could not get anything to work :(

mySQL select one column DISTINCT, with corresponding other columns

SQL Server Distinct Union for one column

Hopefully this is enough information for an answer.

Thank you for your time!

EDIT (Sample Data and Desired Results)

tblFruit_ID, tblFruit_FruitType, tblFruit_FruitName
int          NVarChar            Text
1            Citrus              Orange
2            Citrus              Lime
3            Citrus              Lemon
4            Seed                Cherry
5            Seed                Banana

Results:

1            Citrus
4            Seed

select * from 
(select 
ROW_NUMBER() OVER(PARTITION BY tblFruit_FruitType ORDER BY tblFruit_FruitType DESC) as tt
,*
from tblFruit
) a
where a.tt=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

I needed to do the same and had to query a query to get the result

I set my first query up to bring in all IDs from the table and all other information needed to filter:

SELECT tMAIN.tLOTS.NoContract, tMAIN.ID
FROM tMAIN INNER JOIN tLOTS ON tMAIN.ID = tLOTS.id
WHERE (((tLOTS.NoContract)=False));

Save this as Q04_1 -0 this returned 1229 results (there are 63 unique records to query - soime with multiple LOTs)

SELECT DISTINCT ID
FROM q04_1;

Saved that as q04_2

I then wrote another query which brought in the required information linked to the ID

SELECT q04_2.ID, tMAIN.Customer, tMAIN.Category
FROM q04_2 INNER JOIN tMAIN ON q04_2.ID = tMAIN.ID;

Worked a treat and got me exactly what I needed - 63 unique records returned with customer and category details.

This is how I worked around it as I couldn't get the Group By working at all - although I am rather "wet behind the ears" weith SQL (so please be gentle and constructive with feedback)

I suppose the easiest and the best solution is using OUTER APPLY. You only use one field with DISTINCT but to retrieve more data about that record, you utilize OUTER APPLY.

To test the solution, execute following query which firstly creates a temp table then retrieves data:

 DECLARE @tblFruit TABLE (tblFruit_ID int, tblFruit_FruitType varchar(10), tblFruit_FruitName varchar(50))
 SET NOCOUNT ON
 INSERT @tblFruit VALUES (1,'Citrus ','Orange')
 INSERT @tblFruit VALUES (2,'Citrus','Lime')
 INSERT @tblFruit VALUES (3,'Citrus','Lemon')
 INSERT @tblFruit VALUES (4,'Seed','Cherry')
 INSERT @tblFruit VALUES (5,'Seed','Banana')
   
SELECT DISTINCT (f.tblFruit_FruitType), outter_f.tblFruit_ID
FROM @tblFruit AS f
OUTER APPLY (
    SELECT TOP(1) *
    FROM @tblFruit AS inner_f
    WHERE inner_f.tblFruit_FruitType = f.tblFruit_FruitType
) AS outter_f

The result will be:

Citrus 1

Seed 4

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