SQL mysql - Select distinct UNIQUE but return all columns

Question

SELECT DISTINCT field1  field2  field3           FROM table   I am trying to accomplish the following sql statement but I want it to return all columns is this possible  Something like   SELECT DISTINCT field1    from table

User · Answer

That s a really good question  I have read some useful answers here already  but probably I can add a more precise explanation   Reducing the number of query results with a GROUP BY statement is easy as long as you don t query additional information  Let s assume you got the following table  locations    --country-- --city--  France      Lyon  Poland      Krakow  France      Paris  France      Marseille  Italy       Milano   Now the query   SELECT country FROM locations GROUP BY country   will result in   --country--  France  Poland  Italy   However  the following query  SELECT country  city FROM locations GROUP BY country      throws an error in MS SQL  because how could your computer know which of the three French cities  Lyon    Paris  or  Marseille  you want to read in the field to the right of  France    In order to correct the second query  you must add this information  One way to do this is to use the functions MAX   or MIN    selecting the biggest or smallest value among all candidates  MAX   and MIN   are not only applicable to numeric values  but also compare the alphabetical order of string values   SELECT country  MAX city  FROM locations GROUP BY country   will result in   --country-- --city--  France      Paris  Poland      Krakow  Italy       Milano   or   SELECT country  MIN city  FROM locations GROUP BY country   will result in   --country-- --city--  France      Lyon  Poland      Krakow  Italy       Milano   These functions are a good solution as long as you are fine with selecting your value from the either ends of the alphabetical  or numeric  order  But what if this is not the case  Let us assume that you need a value with a certain characteristic  e g  starting with the letter  M   Now things get complicated   The only solution I could find so far is to put your whole query into a subquery  and to construct the additional column outside of it by hands   SELECT      countrylist          SELECT TOP 1 city      FROM locations      WHERE           country   countrylist country           AND city like  M          FROM  SELECT country FROM locations GROUP BY country  countrylist   will result in   --country-- --city--  France      Marseille  Poland      NULL  Italy       Milano

User · Answer

If I understood your problem correctly  it s similar to one I just had  You want to be able limit the usability of DISTINCT to a specified field  rather than applying it to all the data    If you use GROUP BY without an aggregate function  which ever field you GROUP BY will be your DISTINCT filed    If you make your query   SELECT   from table GROUP BY field1    It will show all your results based on a single instance of field1   For example  if you have a table with name  address and city  A single person has multiple addresses recorded  but you just want a single address for the person  you can query as follows   SELECT   FROM persons GROUP BY name    The result will be that only one instance of that name will appear with its address  and the other one will be omitted from the resulting table  Caution  if your fileds have atomic values such as firstName  lastName you want to group by both   SELECT   FROM persons GROUP BY lastName  firstName    because if two people have the same last name and you only group by lastName  one of those persons will be omitted from the results  You need to keep those things into consideration  Hope this helps

User · Answer

SELECT DISTINCT FIELD1  FIELD2  FIELD3 FROM TABLE1 works if the values of all three columns are unique in the table    If  for example  you have multiple identical values for first name  but the last name and other information in the selected columns is different  the record will be included in the result set

User · Answer

You can do it with a WITH clause   For example   WITH c AS  SELECT DISTINCT a  b  c FROM tableName  SELECT   FROM tableName r  c WHERE c rowid r rowid AND c a r a AND c b r b AND c c r c   This also allows you to select only the rows selected in the WITH clauses query

User · Answer

For SQL Server you can use the dense rank and additional windowing functions to get all rows AND columns with duplicated values on specified columns  Here is an example     with t as       select col1    a   col2    b   col3    c   other    r1  union all     select col1    c   col2    b   col3    a   other    r2  union all     select col1    a   col2    b   col3    c   other    r3  union all     select col1    a   col2    b   col3    c   other    r4  union all     select col1    c   col2    b   col3    a   other    r5  union all     select col1    a   col2    a   col3    a   other    r6     tdr as       select                      total dr rows   count    over partition by dr      from           select                              dr   dense rank   over order by col1  col2  col3               dr rn   row number   over partition by col1  col2  col3 order by other          from              t       x    select   from tdr where total dr rows  gt  1   This is taking a row count for each distinct combination of col1  col2  and col3

User · Answer

From the phrasing of your question  I understand that you want to select the distinct values for a given field and for each such value to have all the other column values in the same row listed  Most DBMSs will not allow this with neither DISTINCT nor GROUP BY  because the result is not determined   Think of it like this  if your field1 occurs more than once  what value of field2 will be listed  given that you have the same value for field1 in two rows but two distinct values of field2 in those two rows    You can however use aggregate functions  explicitely for every field that you want to be shown  and using a GROUP BY instead of DISTINCT   SELECT field1  MAX field2   COUNT field3   SUM field4        FROM table GROUP BY field1

User · Answer

It can be done by inner query   query    SELECT                FROM  SELECT field                 FROM table                 ORDER BY id DESC  as rows                            GROUP BY field

User · Answer

I would suggest using   SELECT    from table where field1 in      select distinct field1 from table     this way if you have the same value in field1 across multiple rows  all the records will be returned

User · Answer

Just include all of your fields in the GROUP BY clause

User · Answer

Great question  aryaxt -- you can tell it was a great question because you asked it 5 years ago and I stumbled upon it today trying to find the answer   I just tried to edit the accepted answer to include this  but in case my edit does not make it in   If your table was not that large  and assuming your primary key was an auto-incrementing integer you could do something like this   SELECT    table   FROM table --be able to take out dupes later LEFT JOIN     SELECT field  MAX id  as id   FROM table   GROUP BY field   as noDupes on noDupes id   table id WHERE     this will result in only the last instance being seen   noDupes id is not NULL

User · Answer

SELECT   from table where field in  SELECT distinct field from table

User · Answer

SELECT   FROM tblname GROUP BY duplicate values ORDER BY ex VISITED ON DESC LIMIT 0   30   in ORDER BY i have just put example here  you can also add ID field in this

User · Answer

You re looking for a group by   select   from table group by field1   Which can occasionally be written with a distinct on statement   select distinct on field1   from table   On most platforms  however  neither of the above will work because the behavior on the other columns is unspecified   The first works in MySQL  if that s what you re using    You could fetch the distinct fields and stick to picking a single arbitrary row each time   On some platforms  e g  PostgreSQL  Oracle  T-SQL  this can be done directly using window functions   select   from      select              row number   over  partition by field1 order by field2  as row number    from table      as rows where row number   1   On others  MySQL  SQLite   you ll need to write subqueries that will make you join the entire table with itself  example   so not recommended

User · Answer

Found this elsewhere here but this is a simple solution that works    WITH cte AS    Declaring a new table named  cte  to be a clone of your table      SELECT    ROW NUMBER   OVER  PARTITION BY id ORDER BY val1 DESC  AS rn  FROM MyTable    Selecting only unique values based on the  id  field        SELECT      Here you can specify several columns to retrieve     FROM cte  WHERE rn   1

User · Answer

Try  SELECT table   FROM table  WHERE otherField    otherValue  GROUP BY table fieldWantedToBeDistinct limit x

User · Answer

SELECT  c2 field1           field2 FROM     SELECT DISTINCT                 field1          FROM   dbo TABLE AS C           AS c1         JOIN dbo TABLE AS c2 ON c1 field1   c2 field1

User · Answer

select min table id   table column1 from table  group by table column1

User · Answer

Add GROUP  BY to field you want to check for duplicates your query may look like  SELECT field1  field2  field3           FROM table GROUP BY field1   field1 will be checked to exclude duplicate records  or you may query like   SELECT    FROM table GROUP BY field1   duplicate records of field1 are excluded from SELECT

[sql] SQL/mysql - Select distinct/UNIQUE but return all columns?

Examples related to sql

Examples related to select

Examples related to distinct