CODEWITHSUNDEEP
sql-serverselect
Steve
Steve

Reputation: 672

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

Upvotes: 39

Views: 181993

Answers (7)

Biplob Das
Biplob Das

Reputation: 3094

SELECT * FROM table_name GROUP BY columnName

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

Upvotes: -1

Muhammad Musavi
Muhammad Musavi

Reputation: 2696

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

Upvotes: 0

Reza Jenabi
Reza Jenabi

Reputation: 4279

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

Upvotes: 4

Muhammed salem
Muhammed salem

Reputation: 1

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

Upvotes: 0

Marcus Black
Marcus Black

Reputation: 1

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)

Upvotes: 0

Thomas
Thomas

Reputation: 64635

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

Upvotes: 26

valex
valex

Reputation: 24134

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

Upvotes: 45

Related Questions