SQL Query Multiple Columns Using Distinct on One Column Only

Question

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

Post sample data and desired results, please. – Aaron Bertrand Aug 13 '12 at 15:26 — Aaron Bertrand, Aug 13 '12 at 15:26

score 44 · Accepted Answer · answered Aug 13 '12 at 15:26

44

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

answered Aug 13 '12 at 15:26

valex

23,966
7
43
60

This query returns the correct rows with the results. Please see the below answer for more explanation of what is happening! – Steve Aug 13 '12 at 15:39

Thomas · Answer 2 · 2012-08-13T20:47:34.487

25

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

edited Aug 13 '12 at 20:47

answered Aug 13 '12 at 15:16

Thomas

63,911
12
95
141

1

Nice. Thank you a ton. This worked perfectly and I actually understand what everything does now. I wish I could vote up more then once. – Steve Aug 13 '12 at 15:30
Note: In this query Min(tblFruit_id) and Min(tblFruit_FruitName) can be from different rows (different Fruits) – valex Aug 13 '12 at 15:30
You are correct. The query gives the same desired results but does return values from other rows. – Steve Aug 13 '12 at 15:37
@valexhome - Sorry, I see what you are suggesting. Yes, that is true however nothing other than the Id and Type are designed in the output so it will make no difference. – Thomas Aug 13 '12 at 20:50

score 4 · Answer 3 · answered Dec 17 '19 at 08:18

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

score 0 · Answer 4 · answered Nov 03 '17 at 14:39

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)

score 0 · Answer 5 · edited Feb 12 '19 at 11:18

0

select * from 
(select 
ROW_NUMBER() OVER(PARTITION BY tblFruit_FruitType ORDER BY tblFruit_FruitType DESC) as tt
,*
from tblFruit
) a
where a.tt=1

edited Feb 12 '19 at 11:18

Anastasios Selmani

3,579
3
32
48

answered Feb 12 '19 at 10:05

Muhammed salem

1

1

Please add some explanation to that code, such that others can learn from it – Nico Haase Feb 12 '19 at 11:43

Muhammad Musavi · Answer 6 · 2020-11-08T03:57:54.547

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

score 0 · Answer 7 · answered Dec 10 '21 at 05:07

0

SELECT * FROM table_name GROUP BY columnName

This will select all from table with distinct values from column columnName

answered Dec 10 '21 at 05:07

Biplob Das

2,818
21
13

GROUP BY isn't supported in latests MySQL and advised not to be used. You can use District instead. – rwzdoorn Nov 30 '22 at 18:37

SQL Query Multiple Columns Using Distinct on One Column Only

7 Answers7

Linked