Select Distinct on One Column, Return Multiple Other Columns (SQL Server)

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.

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

SQL Query Multiple Columns Using Distinct on One Column Only

select * from tblFruit where
tblFruit_ID in (Select max(tblFruit_ID) FROM tblFruit group by tblFruit_FruitType)

Select multiple columns with only one distinct column in sql

Try with GROUP BY clause and MIN function as below

SELECT CurrencyCode, MIN(BuyRate), MIN(SellRate)
FROM  Currencies
GROUP BY CurrencyCode 

Select distinct on 2 columns, but return all columns from SQL Server

You can use partition by name and type as below

SELECT * from(select 
   [name]
  ,[type]
  ,[col1]
  ,[col2]
  ,[col3]
  ,[etc]
  ,[dateAdded]
  ,[ID]
  ,ROW_NUMBER() OVER(Partition by name, type order by dateAdded DESC) rownumber from [dbo].[Table]) a where rownumber = 1;

SELECT DISTINCT on multiple columns along with other columns

SELECT DISTINCT a,b,c FROM t

is roughly equivalent to:

SELECT a,b,c FROM t GROUP BY a,b,c,t

It's a good idea to get used to the GROUP BY syntax, as it's more powerful.
It's a good idea to get used to the GROUP BY syntax,
Please see this post:

Possible duplicate ?

SQL Query for multiple columns with one column distinct

You can use ROW_NUMBER(). For example

select *
from (
  select *,
    row_number() over(partition by case_number) as rn
) x
where rn = 1

The query above will pseudo-randomly pick one row for each case_number. If you want a better selection criteria you can add ORDER BY or window frames to the OVER clause.

Return all columns with distinct on multiple columns in SQL table

Can group by 3 but must use some type of aggregate function like min or max on the other two

select col1, col2, col3, max(col4), min(col5) 
from tbl 
group by col1, col2, col3

---

Related Topics