Sql/MySQL - Select Distinct/Unique But Return All Columns

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

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.

How to select distinct values for two and return all columns?

If you just want the distinct values, use select distinct:

select distinct source, target
from example t;

If you want the rows where the source/target only appears on one row, then one method uses window functions:

select t.*
from (select t.*,
             count(*) over (partition by source, target) as cnt
      from example t
     ) t
where cnt = 1;

SELECT DISTINCT on one column, return multiple other columns (SQL Server)

SELECT * FROM
GPSReport AS G1
JOIN (SELECT device_serial, max(datetime) as mostrecent 
      FROM GPSReport group by device_serial) AS G2
ON G2.device_serial = G1.device_serial and g2.mostrecent = g1.datetime
ORDER BY G1.device_serial

SELECT DISTINCT on one column, with multiple columns in mysql

Like Gordon said but as a subquery to get the appropriate image.

SELECT DISTINCT q.category, ch_offer_id, store_img
FROM
(
    SELECT category, MAX(offer_id) ch_offer_id
    FROM tbl_coupan_offer
    GROUP BY category
) q
JOIN tbl_coupan_offer
ON q.ch_offer_id=tbl_coupan_offer.offer_id

MySQL - Select distinct 2 columns and return all

I would use group by instead:

SELECT unique_id, model, color, slots FROM table_name GROUP BY model, color

Select Distinct/Unique but return all columns

You could use a join on the max id groped by ip eg:

 select * from my_table m 
 inner join  (
    select clientip, max(browseid) as m_id 
    from my_table
    group by clientip
 ) t on t.clientip = m.clientip and t.m_id = m.browseid

---

Related Topics