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 * from table where field in (SELECT distinct field from table)

Answer 2

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.

Answer 3

Just include all of your fields in the GROUP BY clause.

Answer 4

It can be done by inner query

$query = "SELECT * 
            FROM (SELECT field
                FROM table
                ORDER BY id DESC) as rows               
            GROUP BY field";

Answer 5

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

Answer 6

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.