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 · Accepted Answer

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

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

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

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

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

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