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

User · Answer

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

User · Answer

select   from tblFruit where tblFruit ID in  Select max tblFruit ID  FROM tblFruit group by tblFruit FruitType

User · Answer

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

User · Answer

select   from   select  ROW NUMBER   OVER PARTITION BY tblFruit FruitType ORDER BY tblFruit FruitType DESC  as tt    from tblFruit   a where a tt 1

User · Answer

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

User · Answer

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

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

Examples related to sql-server

Examples related to select