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

sql select distinct
aryaxt picture aryaxt · May 25, 2011 · Viewed 886.6k times · Source 
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

Denis de Bernardy picture Denis de Bernardy · May 25, 2011

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.

Related questions

How to select records without duplicate on just one field in SQL?

I have a table with 3 columns like this: +------------+---------------+-------+ | Country_id | country_title | State | +------------+---------------+-------+ There are many records in this table. Some of them have state and some other don't. Now, imagine these records: 1 | Canada | …

Read More
MySQL Counting Distinct Values on Multiple Columns

I wrote a script that runs each time a user logs into a computer in our domain. This script makes a record of the user as well as the computer they logged into. Any number of users can log into …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More