SQL/mysql - Select distinct/UNIQUE but return all

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

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

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